Months dynamically Shown based on MTD,QTD and YTD filter selection.

[Anonymous]

Hello All,

I have bar chart with Months column as shown in below image and a filter for MTD/QTD/YTD. I want to show the month in bar chart as shown in below image based on my selection in  MTD/QTD and YTD filter. 

My fiscal Year start from "May" and end at "April". So for QTD, I should be having only "Aug", "Sep" and "Oct" in the below bar chart. Similarly if i select MTD filter, i should be having only Sep month bar chart and if i select YTD, then i should be seeing all months from May to Sep.

Any help on how this can be achieve.

Thanks!!

[amitchandak]

@Anonymous , for MTD and YTD you can use time intelligence. YTD with year and date

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

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

For Qtr You need Qtr Start Date

Column in date table

Qtr Start Date = DATEADD(STARTOFYEAR('Date'[Date],"4/30"),QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH)
Qtr Month No = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],MONTH)+1
Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

Measures

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 ])))

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))

The calendar file is attached after signature

1.Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

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

[Anonymous]

@amitchandak   

Hi,

I have already calculated the MTD,QTD and YTD. 

My issue is how i can filter out the months on bar chart for based on MTD/QTD and YTD selection.

If select QTD then i should have Aug,sep and Oct and Remove May,Jun,Jul.

Need you help to achieve this!

[amitchandak]

@Anonymous , what date is selected on the page. A measure slicer can be one option.

[Anonymous]

@amitchandak 

 No date is selected.

Please check the below formula:

Mtd_Sales =

var maxdate=MAX(Table[Timeline] )

var selecdate=IF(not(isfiltered('(Table)'[Timeline])),maxdate,SELECTEDVALUE('(Table)'[Timeline]))

return CALCULATE(SUM('(Table)'[Sale]),DATESINPERIOD('(Table)'[Timeline],selecdate,-1,MONTH))

Can we create same formula which can retuen the "Months" from Months column instead of "Sales".

Something like below:

Mtd_Months =

var maxdate=MAX(Table[Timeline] )

var selecdate=IF(not(isfiltered('(Table)'[Timeline])),maxdate,SELECTEDVALUE('(Table)'[Timeline]))

return  ('(Table)'[Months]),DATESINPERIOD('(Table)'[Timeline],selecdate,-1,MONTH))

But i am getting error with the above formula. 

Wanted to check what would be the correct way to have "Text" in return function by keeping all other variable exactly the same. If i am able to include Text in return function then issue will be resolved.

Thanks!!

[amitchandak]

@Anonymous , Try like

Mtd_Months =
var maxdate=MAX(Table[Timeline] )
var selecdate=IF(not(isfiltered('(Table)'[Timeline])),maxdate,SELECTEDVALUE('(Table)'[Timeline]))
return CALCULATE(SUM('(Table)'[Sale]) , values('(Table)'[Months]),DATESINPERIOD('(Table)'[Timeline],selecdate,-1,MONTH))

or

Mtd_Months =
var maxdate=MAX(Table[Timeline] )
var selecdate=IF(not(isfiltered('(Table)'[Timeline])),maxdate,SELECTEDVALUE('(Table)'[Timeline]))
return CALCULATE(SUM('(Table)'[Sale]) ,DATESINPERIOD('(Table)'[Timeline],selecdate,-1,MONTH))

[Anonymous]

@amitchandak  : How to calculate FISCAL QTD with the help of below formula:

Mtd_Months =
var maxdate=MAX(Table[Timeline] )
var selecdate=IF(not(isfiltered('(Table)'[Timeline])),maxdate,SELECTEDVALUE('(Table)'[Timeline]))
return CALCULATE(SUM('(Table)'[Sale]) ,DATESINPERIOD('(Table)'[Timeline],selecdate,-1,MONTH)

Need to calculate the fiscal QTD by using above formula.

Thanks in advance!!

[v-yiruan-msft]

Hi @Anonymous ,

You can refer the content in the following links to get it. 

QTD Function with Custom Fiscal Quarters

Custom time-related calculations

Best Regards

Rena

[Anonymous]

Hi,

I have already calculated the MTD,QTD and YTD. 

My issue is how i can filter out the months on bar chart for based on MTD/QTD and YTD selection.

If select QTD then i should have Aug,sep and Oct and Remove May,Jun,Jul.

Need you help to achieve this!