Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I was trying to get the YTD sales for the selected month-year slicer, it's working as expected if I use the measure in KPI card.
ex:
Month-Year Sales
Jan-2021 £50
Feb-2021 £100
Mar-2021 £250
Apr-2021 £200
I have exisitng Orders table that has Sales information, created a disconnected 'Date' table and measure as below,
YTD=
VAR LDATE = selectedvalue('Date'[Month-year])
VAR M = selectedvalue('Date'[Month])
VAR Y = selectedvalue('Date'[Year])
RETURN
calculate(
sum(orders[Sales]),
orders[year] = Y &&
orders[Month]<=M
)
Note: Aplied "Month-Year" slicer from newly created disconected Date table
Result as KPI card:
when I choose Mar-2021 in slicer it gives me £400 in kpi card as expected. but the issue I am facing is when displaying the result set in bar graph by month'year.
for this, I have used Month-year from orders table in x-axis and YTD calculation as above in y axis and applied "Month-year" slicer from "date" table
result:
Date get filtered for the YTD months, I mean if I choose Mar-2021 in slicer the bar graph shows Jan-2021,Feb-2021,Mar-2021 but with specific sales values not the cumulative YTD in bar grpah.
Expected YTD in bra graph as below,
Jan-2021 £50
Feb-2021 £150
Mar-2021 £400.
Can anyne please help me in getting the cumulative YTD in bar graph for the selected month-year slicer?
Solved! Go to Solution.
Hi @Anonymous ,
Using below dax expression:
YTD =
VAR M =
SELECTEDVALUE ( 'Date'[Month] )
VAR Y =
SELECTEDVALUE ( 'Date'[Year] )
RETURN
IF (
MAX ( 'Order'[Month] ) <= M
&& MAX ( 'Order'[Year] ) = Y,
CALCULATE (
SUM ( 'Order'[Sales] ),
FILTER (
ALL ( 'Order' ),
'Order'[Year] = Y
&& 'Order'[Month] <= M
&& 'Order'[Month] <= MAX ( 'Order'[Month] )
)
)
)
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
@Anonymous , if you select a month and want to display more than that , then you need on joined and one independent table or both independent table
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
YTD QTY forced=
var _max = maxx(allselected(Idate) , Idate[Date]) //Independent Date table
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
or
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
@amitchandak ,
Thanks for the quick reply.
the above logic is giving me the expected aggregation(YTD) but I see issues with the x-axis (month'year).
In my dataset I have data from Aug-2019 to Aug-2021, I have applied Mar-2021 in slicer and the formula below gives me data from Aug-2019 to Aug-2021 with expected YTD aggregation for Jan,feb,Mar 2021 and remainings months in 2021 have the same YTD value of Mar-2021 .
YTD QTY forced=
var _max = maxx(allselected(Idate) , Idate[Date]) //Independent Date table
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
The alternate formula below is giving Jan-2021 to Aug-2021 in x-axis with YTD value but the Mar-2021 aggregated YTD is distributed to all other months after Mar-2021.
YTD QTY forced=
var _max = maxx(allselected(Idate) , Idate[Date]) //Independent Date table
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
Expected result:
YTD agregation is giving me the expected result, but need to fix the axis to YTD months rather whole months data.
Ex: If we have data from Aug-2019 to Aug-2021, when choosing Mar-2021 in slicer the bar graph should show the Jan-2021, Feb-2021,Mar-2021 with YTD aggregation.
hope this details the requirement and please do needful possibly.
Thanks
Hi @Anonymous ,
Using below dax expression:
YTD =
VAR M =
SELECTEDVALUE ( 'Date'[Month] )
VAR Y =
SELECTEDVALUE ( 'Date'[Year] )
RETURN
IF (
MAX ( 'Order'[Month] ) <= M
&& MAX ( 'Order'[Year] ) = Y,
CALCULATE (
SUM ( 'Order'[Sales] ),
FILTER (
ALL ( 'Order' ),
'Order'[Year] = Y
&& 'Order'[Month] <= M
&& 'Order'[Month] <= MAX ( 'Order'[Month] )
)
)
)
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Hi..
i dont have date column in sales table.only have month and year.and i have dim date table with date column ,i tried the given dax but not working for me .i need the exact answer like you given showns as image.
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
71 |