cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vardhan918
Frequent Visitor

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

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?

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Hi  @Vardhan918 ,

 

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:

vkellymsft_0-1630575161500.png

For the related .pbix file,pls see attached.

 

 

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@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  @Vardhan918 ,

 

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:

vkellymsft_0-1630575161500.png

For the related .pbix file,pls see attached.

 

 

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.