Cumulative YTD in Bar graph for selected month-year in slicer

[Anonymous]

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?

[amitchandak]

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

[Anonymous]

@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

[v-kelly-msft · Accepted 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!

[Vidya1882]

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.