cancel
Showing results for
Did you mean:
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
Community Support

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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

3 REPLIES 3
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!
Frequent Visitor

@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

Community Support

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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

Announcements

#### Launching new user group features

Learn how to create your own user groups today!