Forum Discussion

H3nning's avatar
H3nning
Helper V
3 years ago

Previous Period as single Value

Hi,   Im experimenting with SCD2 data and I have a problem with a time intelligence related calculation. To keep it simple Id rather not explain the whole complexity of the context.   I have a ta...
Need Help
some_bih's avatar
some_bih
Community Champion
3 years ago

Hi H3nning as you are on beginning of your project, I suggest to check link about different date calculation in DAX.

Find your scenario, it could be MTD, PMC, PM some other concerning months. Benefit of this solution is that in future you can easly adopt rest of time intelligence calculation which currently you do not need. 

Did I answer correctly? Kudos appreciate / accept solution.

Link: https://www.daxpatterns.com/standard-time-related-calculations/ 

H3nning's avatar
H3nning
Helper V
3 years ago

Hi, thank you, but I couldn't find a solution there. I tried two approaches. With ISINSCOPE it seems it does not work with an automatic hierarchy. My resultset is always empty. I used

SWITCH(TRUE,

   ISINSCOPE(Date.Day), ~Calculation for day level~

   ,ISINSCOPE(Date.Month), ~Calculation for month level~

   ,ISINSCOPE(Date.Quarter), ~Calculation for quarter level~

   ,ISINSCOPE(Date.Year), ~Calculation for year level~

 

The result is always empty, although i tested the calculation for month level and it is correct. But even on month level it looks like this:

 

 

The other thing I thought about is PARALLELPERIOD, but the issue with that is, that it returns a table of dates. But I need a single value to compare it to my SCD2 data. MIN(PARALLELPERIOD()) is also not possible, because MIN requires a column, not a table.

  • some_bih's avatar
    some_bih
    Community Champion
    3 years ago

    Hi H3nning I usually find my scenario on link from SQLBI.