Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!!
@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
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.
No date is selected.
Please check the below formula:
@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))
@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
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!
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |