Trying to use Dax to compare the Current Sales to the MTD Sales

1
votes

okay so basically lets say in the month of December (Using a Slicer of Dec 1st to Dec 10th)

I want to get 2 numbers the MTD and the sales for only the current date

so in this case I want to Compare Dec 10th to the entire MTD for December

the MTD Part was easy

CY Bookings = SUMX ( Query1, Query1[final_booked_count] )

which follows what the slicer says,

but i'm at a loss for how to tell it to pull the sales for the MAX DATE IN THE SLICER

1
powerbidax

1 Answers

0
votes

To get the value of the last date in the slicer, you should change the context filter using CALCULATE. An easy way to do so is like follows

CY Bookings Last Date =
VAR MaxDate =
    MAX( Query1[date] )
RETURN
    CALCULATE(
        SUMX(
            Query1,
            Query1[final_booked_count]
        ),
        Query1[date] = MaxDate
    )

If you have a model with a Date table, then the code would change to

CY Bookings Last Date =
VAR MaxDate =
    MAX( 'Date'[Date] )
RETURN
    CALCULATE(
        SUMX(
            Query1,
            Query1[final_booked_count]
        ),
        'Date'[Date] = MaxDate
    )