Deloitte: Power BI Interview | 2 Scenario based questions

Thanks a lot sir for the video🙏
Please bring more videos frequently.

Thank You Sir for Sharing 👌❤️

Current_MTD_Excluding_MaxDate =
CALCULATE(
TOTALMTD(
SUM('Sample Data File - Deloitte May xlsx - Sheet1'[Revenue]),
'Sample Data File - Deloitte May xlsx - Sheet1'[Date]
),
'Sample Data File - Deloitte May xlsx - Sheet1'[Date] <
MAX('Sample Data File - Deloitte May xlsx - Sheet1'[Date])
)
what about this version

Thanks for sharing👍🏻

Nice video as always

Audio in previous videos is Sound. Audio in the present video is Noise.

💯💯💥

Is power apps compulsory for job along with power bi

Plz post videos frequently

Sir pls purchase a Mike
Sound quality is not up to the Mark

We can use Mtd function right ?

For scenario 1 instead of creating a new column use of Table.TransformColumns with Text. Select function will be more efficient given that the existing column will no longer be used.

👍👍

2nd Question easy dax mesure is
2 days rev = CALCULATE(SUM(Revenue[Revenue]),DATESINPERIOD(Revenue[Date],MAX(Revenue[Date])-1,-2,DAY))

For the second scenario this will work:

MtdValue=CALCULATE (
TOTALMTD ( SUM(Fact[Revenue] ),
'DateTable'[Date] ),
'DateTable'[Date] < TODAY ()
)

MtdValue=CALCULATE (
SUM(Fact[Revenue] ),
Datesmtd('DateTable'[Date] ),
'DateTable'[Date] < TODAY () )

for first scenerio , use column by example where you will get only the numbers of the solution

Very good scenarios sir, can we please start a series with this DAX scenarios?

This is also the other way
Total =
CALCULATE(
SUM(Data[Revenue]),
DATESINPERIOD(Data[Date],MAX(Data[Date])-1,-3,DAY)
)

My approach with simple CALCULATE :
Revenue_total = CALCULATE(SUM('Sheet1 (2)'[Revenue]),'Sheet1 (2)'[Date]<MAX('Sheet1 (2)'[Date]),MONTH('Sheet1 (2)'[Date])=MONTH(NOW()))

Finished watching

for the second question, we can achieve the same output using the below dax code,

SalesMTD_Exclude_CurrentMonth =
VAR _startDate = EOMONTH(TODAY(), -1) + 1
VAR _endDate = TODAY() - 1
VAR _range = DATESBETWEEN('Fact'[Date], _startDate, _endDate)
RETURN
CALCULATE(SUM('Fact'[Revenue]), _range)

for the first question, we can use M Script and create a new custom column and add the below code..


Text.Select([ID], {"0".."9"})

let me know your feedback...

thanks for the video...really useful

Radhe Radhe 🙏

2nd scenario alternative code

amount till march 2nd = CALCULATE(
sum(Sheet1[Revenue]),
DATESBETWEEN(Sheet1[Date], Date(year(today()), month(TODAY()),1) , TODAY()-4)
)

Hi sir,
Thanks for sharing such informative video. I have solved the 2nd question using below measure. It worked fine. What do u say is there anything to change or this will work fine.

Total revenue = CALCULATE(SUM(Sheet1[Revenue]),
FILTER(Sheet1,
Sheet1[Date].[Month] = SELECTEDVALUE(Sheet1[Date].[Month])&& Sheet1[Date] < TODAY()))

👍🏻👍🏻

Mic to thik kar lo

Dax for 2nd Scenario:
total_revenue =
var a=MONTH(TODAY())
RETURN
CALCULATE(SUM('Sheet1 (2)'[Revenue]),FILTER('Sheet1 (2)',MONTH('Sheet1 (2)'[Date])=a && 'Sheet1 (2)'[Date]<TODAY()))

Final_Total_MTD =
TOTALMTD(SUM(fact[Revenue]),fact[Date]) -
calculate(SUM(fact[Revenue]),FILTER(fact,fact[Date] = TODAY()))

for the second question we can use totalMTD directly right, since we have filter option within totalmtd function. we can directly use like : i have tried with different names

totalmtd = TOTALMTD(
SUM(Revenue[Revenue]),
Revenue[Date],
FILTER(
Revenue,
Revenue[Date] < TODAY()
)
)

@learnwidgiggs why cant we use below DAX query this will also give same results. Could you please check
Total MTD = TOTALMTD(sum(Sheet1[Revenue]),Sheet1[Date],Sheet1[Date] < TODAY())

Hello Sir,
After being unemployed for 4 years, finally I got my first job as Power BI Developer for Nokia by gnap services.

All' credit goes to your free Yt interview and questions videos, really they are alot to boost confidence for the interview.

Again big thanks 🙏🙏

Thanks for the video sir. I have a small doubt. Can we not use startofmonth function to calculate May1st date instead of EO?

Total_MTD = TOTALMTD(SUM(Deloitte[Revenue]),Deloitte[Date],FILTER(Deloitte,Deloitte[Date]<TODAY()))

your videos are really very helpful. just wanted to ask do companies provide work from home or hybrid work type for data analysts as well like IT Professionals are getting nowadays???

finding current month sales except todays sales , i got answer by this function [ currentmonsal = TOTALMTD(SUM('Sheet1 (2)'[Revenue]), 'Sheet1 (2)'[Date], 'Sheet1 (2)'[Date]<>TODAY())]

More Robust DAX

Measure =
VAR mxdate =
CALCULATE ( MAX ( test[Date] ), ALL ( test ) )
VAR mxmonth =
MONTH ( mxdate )
VAR mxyear =
YEAR ( mxdate )
RETURN
CALCULATE (
SUM ( test[Revenue] ),
KEEPFILTERS (
test[Date] < mxdate
&& MONTH ( test[Date] ) = mxmonth
&& YEAR ( test[Date] ) = mxyear
)
)

Thanks for your advanced scenario based videos. I tried in the same method of removing unwanted characters form the Id Column. It is not working for me. Took the same data as u took in your video. Pl. help me.

Hello Shashank sir,
As usual thanks for sharing such insightfull video again.

One more thing I wanted to let you know that because of your videos helped me to crack and I got selected into Accenture, it's been 3.5 months now but because of your video especially dax videos helped a lot for preparing interviews.

Keep sharing and posting your video. It is a daily reminder to learn something new always❤

This is working
Total MTD = TOTALMTD(SUM('Fact'[Revenue]),'Fact'[Date]<TODAY())

Simple code that we can use is
MTD with Today -1 = CALCULATE(SUM(tblRevenue[revenue]),DATESMTD(tblRevenue[date]),tblRevenue[date] < TODAY())

last_two_days =

VAR L1 = TODAY() -1
var L2 = TODAY() -2
return
CALCULATE( SUM(Last_two_day_sales[Amt]) , DATESBETWEEN(Last_two_day_sales[Date],L2,L1))