Cumulative YTD only for the current week on visual (x-axis)

[vaspiliop]

Hello,

I use the below DAX for a YTD  cumulative and it works fine. Dates are well linked and when i drill-down to week or select it works fine.

YTD = calculate(sum(Orders[Orders]),filter(all('Orders'),'Orders'[calendarWeek]<=max('Orders'[calendarWeek])))

The issue I have is that I would like to show on the below graph with the same calculation only the August bar, without unfiltering Jan-July (need to use this measure in another graph too , where Jan-July have other measures)

Can you please advise how could i do that? Tried some filter functions without success.

Many thanks!

[v-xicai]

Hi @vaspiliop ,

You may create column and measure like DAX below, and put [Max Month YTD] into Values box of chart visual.

Column:

YearMonth = VALUE(FORMAT('Orders'[Date],"yyyymm"))


Measure:

Max Month YTD =
VAR _MaxYearMonth =
    CALCULATE ( MAX ( 'Orders'[YearMonth] ), ALLSELECTED ( 'Orders' ) )
RETURN
    IF (
        MAX ( 'Orders'[YearMonth] ) = _MaxYearMonth,
        CALCULATE ( [YTD], FILTER ( 'Orders', 'Orders'[YearMonth] = _MaxYearMonth ) ),
        BLANK ()
    )

>>to keep some  filters   working?  since the ALL function clears filters also from other fields where i would need them to still filter.

In addition , for this question, you could change the ALL function to ALLSELECTED function.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@vaspiliop ,

YTD = calculate(sum(Orders[Orders]),filter(all('Orders'),'Orders'[calendarWeek]<=max('Orders'[calendarWeek])))

Check if this can work

Last Month YTD = var _max =maxx(ALLSELECTED('Date'),'Date'[Month Year])
return
CALCULATE([YTD],FILTER('Date','Date'[Month Year]=_max))

[vaspiliop]

Many thanks @amitchandak  , appreciate your help! this actually solved it - just one more question please

is there a way for this formula:

YTD = calculate(sum(Orders[Orders]),filter(all('Orders'),'Orders'[calendarWeek]<=max('Orders'[calendarWeek])))

to keep some  filters   working?  since the ALL function clears filters also from other fields where i would need them to still filter.

thanks