CODEWITHSUNDEEP
powerbidax
ggv
ggv

Reputation: 109

Show cumulative SalesAmount column chart to max date in slicer

I am looking to build a column chart in Power BI that displays cumulative sales by month up to max date selected in date slicer. I have a supplementary Date2 table that is related to Date table (inactive relation). Tried something like this, but I keep getting all the months irrespective which one is selected in slicer:

Sales to date selected = 
VAR ReferenceDate = MAX ( 'Date'[Date] )
Return
    CALCULATE (  [SalesAmount],
        ALLEXCEPT('Date', 'Date'[Year]),
        FILTER( all(Date2[Date]), Date2[Date] <= MAX( Date2[Date] ) 
                               && Date2[Date] <= ReferenceDate),
        USERELATIONSHIP ( 'Date'[Date], Date2[Date] )
    )

This is what I get as a result (Slicer selects month 7): enter image description here

It looks like CALCULATE does not honor filter Date2[Date] <= ReferenceDate . What am I doing wrong here?

Upvotes: 0

Views: 1028

Answers (2)

Ozan Sen
Ozan Sen

Reputation: 2615

I made a correction to your code; but It seems that It takes a long time for it to be approved by community members:

So test this:

Sales to date selected =
VAR ReferenceDate =
    MAX ( ALLSELECTED ( 'Date2'[Date] ) )
RETURN
    CALCULATE (
        [SalesAmount],
        FILTER ( ALLEXCEPT ( 'Date2', 'Date2'[Year] ), 'Date2'[Date] <= ReferenceDate )
    )

Or This:

Sales to date selected =
VAR ReferenceDate =
    MAX ( ALLSELECTED ( 'Date2'[Date] ) )
RETURN
    CALCULATE (
        [SalesAmount],
        FILTER ( ALL ( 'Date2'[Date] ), 'Date2'[Date] <= ReferenceDate )
    )

Upvotes: 0

ggv
ggv

Reputation: 109

OK, looks like this scenario solves my issue. I removed inactive relationship between Date and Date2 and introduced active relationship from Sales to Date2.

Sales to date selected = 
VAR ReferenceDate = MAX ( 'Date'[Date] )
Return
IF( MAX(Date2[Date]) <= ReferenceDate,
    CALCULATE (  [SalesAmount],
        ALLEXCEPT('Date', 'Date'[Year]),
        Date2[Date] <= MAX( Date2[Date] )  
    )  
)

Upvotes: 0

Related Questions