Forum Discussion

swathy429's avatar
swathy429
Regular Visitor
5 years ago

Help with DAX query

Hello Team,   I was trying to calculate a Card value using a DAX query. I have a set of data where the DAX formula should perform like   By taking the current Fiscal Quarter  we need to calculate...
General Comment
Need Help
Tips and Tricks
amitchandak's avatar
amitchandak
Super User
5 years ago

swathy429 , if you have date, with date and time intelligence you can have

Month vs month

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

 

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))

Rolling 3 till last 1 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],Startofmonth(dateadd(Sales[Sales Date],-1,month)),3,MONTH))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

swathy429's avatar
swathy429
Regular Visitor
5 years ago

Thanks for the reply amitchandak  ,

I dont have any dates in the data as you see in the sample data that's all column I Have