Forum Discussion

mark_carlisle's avatar
mark_carlisle
Advocate IV
6 years ago
Solved

Date What If Parameter

I've created 3 What If parameters Date - Year, Date - Month, and Date - Day.

 

I have a two measures already to calculate the total cases pre and post a date, these are;

 

 

Total Cases (Post 16/03) = 
CALCULATE (
    [Total Cases (Distinct)],
    ALL ( 'Calendar' ),
    FILTER ( 'Calendar', 'Calendar'[Date] >= DATE ( 2020, 03, 16 ) )
)
Total Cases (Pre 16/03) = 
CALCULATE (
    [Total Cases (Distinct)],
    ALL ( 'Calendar' ),
    FILTER (
        'Calendar',
        AND (
            'Calendar'[Date] >= DATE ( 2020, 01, 01 ),
            'Calendar'[Date] < DATE ( 2020, 03, 16 )
        )
    )
)

 

 

What I wanted to do was replace the hardcoded date with the values from the What If parameters so the users can move this date, so if we take Total Cases (Pre 16/03) I've done this;

 

 

Total Cases (Pre 16/03) = 
CALCULATE (
    [Total Cases(Distinct)],
    ALL ( 'Calendar' ),
    FILTER (
        'Calendar',
        AND (
            'Calendar'[Date] >= DATE ( 2020, 01, 01 ),
            'Calendar'[Date] < DATE ( [Date - Year Value], [Date - Month Value], [Date - Day Value] )
        )
    )
)

 

 

This returns nothing and when troubleshooting its the parameters causing the issue as it returns a blank calendar table.

 

Is what I want to do possible?

  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Try:

     

    Total Cases (Pre 16/03) = 
VAR __Year = SELECTEDVALUE([Date - Year Value])
VAR __Month = SELECTEDVALUE([Date - Month Value])
VAR __Day = SELECTEDVALUE([Date - Day Value])
CALCULATE (
    [Total Cases(Distinct)],
    ALL ( 'Calendar' ),
    FILTER (
        'Calendar',
        AND (
            'Calendar'[Date] >= DATE ( 2020, 01, 01 ),
            'Calendar'[Date] < DATE ( __Year, __Month, __Day )
        )
    )
)

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Try:

     

    Total Cases (Pre 16/03) = 
VAR __Year = SELECTEDVALUE([Date - Year Value])
VAR __Month = SELECTEDVALUE([Date - Month Value])
VAR __Day = SELECTEDVALUE([Date - Day Value])
CALCULATE (
    [Total Cases(Distinct)],
    ALL ( 'Calendar' ),
    FILTER (
        'Calendar',
        AND (
            'Calendar'[Date] >= DATE ( 2020, 01, 01 ),
            'Calendar'[Date] < DATE ( __Year, __Month, __Day )
        )
    )
)
  • mark_carlisle's avatar
    mark_carlisle
    Advocate IV
    6 years ago

    Tried the same thing earlier and it didn't work, but tried yours anyway and then realised it was because I didn't have the Sync Slicers option set correctly!

     

    Thanks for the help.