Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I needed to calculate YTD value for a group of Units for two different year and I was able to do and plot a graph. The formulae I used was
YTDXYX = CALCULATE(SUM(Data[XYX days]);FILTER(ALLSELECTED(Data[Month]);'Data'[Month]<=MAX('Data'[Month])))
This gave me data based on month and the value I see in my tabular column is like this which is correct:
Month 2017 2018
1 123 110
2 312 253
3 444 375
4 516 500 and so on... Now this value is correct and I have no issue.
But when I try to filter out and put a visual level filter just for Month like Month is 4 in the above tabular column
I get data as
Month 2017 2018
4 72 125 which is the actual value and not the YTD value. May I know what is causing this issue . I need to put the actual month and get the YTD for different Units and so this filter is important.
Its not behaving correctly though.
Please advice.
Regards,
Ankku
Hi,
Try this
=CALCULATE(SUM(Data[XYX days]);DATESYTD(Calendar[Date];"31/12"))
Please note the following:
Hope this helps.
Hi @Anonymous,
This is due to the function ALLSELECTED(), the visual level filter will affect the measure values.
To get the value:
Month 2017 2018
4 516 500
You can replace the ALLSELECTED() function with ALL() function, so the new measure is
YTDXYX = CALCULATE(SUM(Data[XYX days]);FILTER(ALL(Data[Month]);'Data'[Month]<=MAX('Data'[Month])))
Best Regards,
Qiuyun Yu
Hi @v-qiuyu-msft,
I still see something is wrong in here. if you see my graph here I get the value as YTD in the graph as below. Please see the value for month 4. The value in graph is different and some other value when I put a filter on month 4. Even in graph if I put the filter as Month 4 I get the same value as in tabular column. But without filter there is a different value in the graph as you can see. What am I missing??
Hi @Anonymous,
Please share pbix file with us if possible.
Best Regards,
Qiuyun Yu