Forum Discussion

maverickf17's avatar
maverickf17
Helper I
11 months ago
Solved

Field parameters date issue

Hi All, I am using a Field parameter calendar date. ---------------------------------------------------- ----------------------------------------------------------------- I am trying to c...
  • MFelix's avatar
    11 months ago

    Hi maverickf17 ,

     

    Let me start by the easy part the question is the context of the switch measure the calculations inside the switch are done in order and if it is true then it's stops and returns that calculation.

     

    In your case you have the first value to be check is the ISINSCOPE ( 'Calendar'[Date].[Year] ) since the year is part of your table the result will always fall back to this one so giving you previous year value:

     

     

    If you redo the order like this:

    Prev Test = 
    SWITCH (
        TRUE (), 
        ISINSCOPE ( 'Calendar'[Date].[Day] ),
            CALCULATE (
                [All Card Transactions],
                DATEADD ( VALUES ( 'Calendar'[Date] ), -1, DAY )
            ),
        ISINSCOPE ( 'Calendar'[Date].[Month] ),
            CALCULATE (
                [All Card Transactions],
                DATEADD ( VALUES ( 'Calendar'[Date] ), -1, MONTH )
            ),
        ISINSCOPE ( 'Calendar'[Date].[Quarter] ),
            CALCULATE (
                [All Card Transactions],
                DATEADD ( VALUES ( 'Calendar'[Date] ), -1, QUARTER )
            ),
        ISINSCOPE ( 'Calendar'[Date].[Year] ),
            CALCULATE (
                [All Card Transactions],
                DATEADD ( VALUES ( 'Calendar'[Date] ), -1, YEAR )
            )
       
    )
    

    Starting on the lowest  value day then you get the expected result:

     

     

     

    However  I would advise you to be carefull with the  auto date-time option since the best practice for the usage of time intelligence calculations is to turn off auto date-time and create a full calendar table in your model, and also mark as a date table.

     

    If you have adjust your calendar table to the format below and then redo your parameter table and the calculation like the formula below you should get expected result:

     

    Date param_ = {
        ("Quarter", NAMEOF('Calendar'[Quarter]), 0),
        ("Month", NAMEOF('Calendar'[Month]), 1),
        ("Day", NAMEOF('Calendar'[Day]), 2)
    }
    
    Prev Test = 
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Calendar'[Day] ),
            CALCULATE (
                [All Card Transactions],
                DATEADD ( VALUES ( 'Calendar'[Date] ), -1, DAY )
            ),
        ISINSCOPE ( 'Calendar'[Month] ),
            CALCULATE (
                [All Card Transactions],
                DATEADD ( VALUES ( 'Calendar'[Date] ), -1, MONTH )
            ),
        ISINSCOPE ( 'Calendar'[Quarter] ),
            CALCULATE (
                [All Card Transactions],
                DATEADD ( VALUES ( 'Calendar'[Date] ), -1, QUARTER )
            ),
        ISINSCOPE ( 'Calendar'[Year] ),
            CALCULATE (
                [All Card Transactions],
                DATEADD ( VALUES ( 'Calendar'[Date] ), -1, YEAR )
            )
      
    )
    

     

    Once again the order of the calculations is very important.

     

     

    You can always create you own date hierarchy using the right click on the column of the year and then 

     

    add the other columns also 

     

    Check some links about this subject but there are a lot more

    https://www.sqlbi.com/articles/automatic-time-intelligence-in-power-bi/

    https://data-mozart.com/tiq-part-1-how-to-destroy-your-power-bi-model-with-auto-date-time/

    https://www.sqlbi.com/articles/mark-as-date-table/

     

     

    Please see file attach.