Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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.

 

Months changes dynamically.JPG

 

 

 

 

Any help on how this can be achieve.

 

Thanks!!

8 REPLIES 8
amitchandak
Super User
Super User

@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
Not applicable

@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!

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

Anonymous
Not applicable

@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!!

@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
Not applicable

@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!!

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.