Field parameters date issue

[maverickf17]

Hi All,
I am using a Field parameter calendar date.
----------------------------------------------------

-----------------------------------------------------------------

I am trying to calculate the dynamic variance based on Date ffield paramters.
But I am getting the below  output

Instead of 

-------------------------------------------------------------------------
I would appreciate if someone can do the chnages in the Pbix file.
The link to the pbix file is 

https://www.dropbox.com/scl/fi/nrmsq1ewbz86b1wjejm8h/Field_Parameters_yoy.pbix?rlkey=piu2nre1zv4nexg... 

The Page name is Field parameters data yoy.

-------------------------------------------------------------------------
Thanks,
ARJUN 

[Ashish_Mathur]

Hi,

From your Matrix visual, just remove the Date Field and drag the Year field from the Calendar table.  Your measure will work fine.  You may also use visual calculations to get the previus row's value

[v-lgarikapat]

@Ashish_Mathur , 

Thanks for your prompt response, I really appreciate it

Best Regards,

Lakshmi

[v-lgarikapat]

Hi @maverickf17 ,

Thanks for reaching out to the Microsoft fabric community forum.

@MFelix 

Thanks for your prompt response

@maverickf17 , 

I wanted to follow up and confirm whether you’ve had the opportunity to review the information  provided by @MFelix  If you have any questions or need further clarification, please don’t hesitate to reach out.

We truly appreciate your continued engagement and thank you for being an active and valued member of the community.

We look forward to hearing from you.

Best regards,
Lakshmi

[MFelix · Accepted solution]

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.