cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bo-oz Frequent Visitor
Frequent Visitor

Can U also do filter to date calculations?

I'd like to show a graph that shows a YTD value for a measure, that also responds to a date filter. So suppose I have the following values:

 

Month  Sales  YTD

01         10      10

02         30      40

03         25      65

 

 When I would filter my report date filter to only show the from february and onwards, the YTD value should not take the 10 into account, so the YTD value for march should be 55 instead.

 

I already tried using DATESBETWEEN, but that takes the filter context of the graph into account. Is there some way to use the minimum date from the date filter, and the maximum date from the graph?

 

Thanks

 
 
2 REPLIES 2
ChrisMendoza Established Member
Established Member

Re: Can U also do filter to date calculations?

@bo-oz,

 

Will this work for you?

 

YTD = 
CALCULATE(
    SUMX(
        Table1,
        Table1[Sales]
    ),
    FILTER(
        ALLSELECTED(Table1),
            Table1[Month] <= MAX(Table1[Month])
    )
)

         

3.PNG

Community Support Team
Community Support Team

Re: Can U also do filter to date calculations?

hi, @bo-oz

After my research, the formula for TOTALYTD Function is equivalent to the formula for DATESYTD Function 

For example:

[YTD Sales] := TOTALYTD ( [Sales Amount], 'Date'[Date] )

[YTD Sales] := CALCULATE ( [Sales Amount], DATESYTD ( 'Date'[Date] ) )

and DATESYTD ( 'Date'[Date] )  corresponds to a filter over the date column using FILTER called by CALCULATETABLE, such as in the following code:

CALCULATETABLE (
    FILTER (
        ALL ( 'Date'[Date] ),
        AND (
            'Date'[Date] <= MAX ( 'Date'[Date] ),
            YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) )
        )
    )
)

So you could use ALLSELECTED to modified formula as below:

Measure 2 = 
CALCULATE (
    SUM ( Table1[Sales] ),
    FILTER (
        ALLSELECTED ( 'Date'[Date] ),
        AND (
            'Date'[Date] <= MAX ( 'Date'[Date] ),
            YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) )
        )
    )
)

Result:

10.PNG

Best Regards,

Lin

 

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