Time Intelligence DAX formula

[damit23183]

Hi,

I do have a COUNT measure and now i would like to calculate count based on following criteria ;

Current year Count which is YTD till now (I got that by TOTALYTD syntex),

Count till now based on Month,

Count till now based on Quarter

same concept i would like to apply for all fiscal Year columns which is different than caledar year.

So, want to count based on year to date for fiscal year, Month to date for fiscal month and Quarter to date for Fiscal quarter.

NOTE: Calendar year is start from january to December. Fiscal year is starting from October to September. I have all fiscal year columns. 

Thanks

[Anonymous]

Hi @damit23183,

You can also take a look at the following blog to use the date function to achieve time intelligence. They are more agility than time intelligence functions and not require a calendar table and they can be nested with other functions:

Time Intelligence "The Hard Way" (TITHW)  
Regards,

Xiaoxin Sheng

[VijayP]

@damit23183 

https://drive.google.com/file/d/1OVUOj2B2iGL0xw7ZxeB3jRROMjEFJvDV/view?usp=sharing

Please check thsi file

[damit23183]

Hi Vijay,

THank you for your response. 

Yes YTD was already working challenge for me is FISCAL YEAR, Month, Quarter calculation.

Thanks

[amitchandak]

@damit23183 , For Oct-Sep year totalytd a and dates ytd should work

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"9/30"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"9/30"))

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date]))) // Standard Qtr
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

// Non standard

column needed

Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

measure
This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

This QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank]) && [Qtr Month No ] <=max([Qtr Month No ])))
Last QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1 && [Qtr Month No ] <=max([Qtr Month No ])))

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

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

[damit23183]

Thanks Amit for your response.

I will try to apply all logic that you have mentioend and see how it works.

However, i dont see or may be i have not understood on how to count with Fiscal year, Month and QUarter? 

I feel Fiscal year calculation is more challenging for me than calendar year calculation.

Thanks