Forum Discussion

nele's avatar
nele
Frequent Visitor
5 years ago
Solved

Measure evolution between periods

Hello,

 

I am trying to measure an evolution between the scores of previous period and last period. The following expression works:

 

Development = (CALCULATE(AVERAGE(PSScoreHist[Score]),PSScoreHist[Period]="2021P07")-CALCULATE(AVERAGE(PSScoreHist[Score]),PSScoreHist[Period]="2021P06"))/100
 
However, I want to softcode the periods "2021P07" and "2021P06". These values are categorical in nature. I want to use something like: MAX(PSScoreHist[Period]) , this gives "2021P07". But I can't use it in my expression since it gives the following error: "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
 
For the previous period "2021P06", I can't find any expression to find it. I tried MAX(PSScoreHist[Period])-1, but this doesn't work.
 
Has anyone an idea how I can fix this?
 
Thank you in advance.
Need Help
  • nele's avatar
    nele
    5 years ago

    Thank you for your response.

     

    I can indeed create a date column out of the period column. However, if I use the expression below, I don't indicate that month 1 needs to use only the last period.

    _Final = 

Var Month1 = AVERAGE('Table'[Column1])
Var Month2 = CALCULATE(AVERAGE('Table'[Column1]),PARALLELPERIOD('Table'[Date],-1,MONTH))

RETURN 
(Month1-Month2) /100

     

    I tried to use the expression below, but that did not work.

     

    Var Month1 = CALCULATE(AVERAGE(PSScoreHist[Score]),PARALLELPERIOD(PSScoreHist[Month],0,MONTH))
     
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi nele ,

    You can update the formula of your measure [Development] as below:

    Development =
VAR _lastperiod =
    CALCULATE ( MAX ( PSScoreHist[Period] ), ALLSELECTED ( PSScoreHist ) )
VAR _preperiod =
    CALCULATE (
        MAX ( PSScoreHist[Period] ),
        FILTER ( ALLSELECTED ( PSScoreHist ), PSScoreHist[Period] < _lastperiod )
    )
RETURN
    (
        CALCULATE ( AVERAGE ( PSScoreHist[Score] ), PSScoreHist[Period] = _lastperiod )
            - CALCULATE ( AVERAGE ( PSScoreHist[Score] ), PSScoreHist[Period] = _preperiod )
    ) / 100

    Best Regards

8 Replies

  • FarhanAhmed's avatar
    FarhanAhmed
    Icon for Community Champion rankCommunity Champion
    5 years ago

    If you are having consistent data in Period then you should create a Date column out of it and change the new column data type to date

    Date = LEFT(PSScoreHist[Period],4) & "-" & RIGHT(PSScoreHist[Period],2) &"-01"

     

    Once this is done now you can use below to create Average Variance (Replace appropriate column)




     

    _Final = 

Var Month1 = AVERAGE('Table'[Column1])
Var Month2 = CALCULATE(AVERAGE('Table'[Column1]),PARALLELPERIOD('Table'[Date],-1,MONTH))

RETURN 
(Month1-Month2) /100
  • nele's avatar
    nele
    Frequent Visitor
    5 years ago

    Thank you for your response.

     

    I can indeed create a date column out of the period column. However, if I use the expression below, I don't indicate that month 1 needs to use only the last period.

    _Final = 

Var Month1 = AVERAGE('Table'[Column1])
Var Month2 = CALCULATE(AVERAGE('Table'[Column1]),PARALLELPERIOD('Table'[Date],-1,MONTH))

RETURN 
(Month1-Month2) /100

     

    I tried to use the expression below, but that did not work.

     

    Var Month1 = CALCULATE(AVERAGE(PSScoreHist[Score]),PARALLELPERIOD(PSScoreHist[Month],0,MONTH))
     
    • FarhanAhmed's avatar
      FarhanAhmed
      Icon for Community Champion rankCommunity Champion
      5 years ago

      In the Above DAX , Month1 is your current Month which is selected through your filter.

      Month2 is preceeding month which in your case if you select July in filter then Month1 is July and Month2 is June.

       

       CALCULATE(AVERAGE(PSScoreHist[Score]),PARALLELPERIOD(PSScoreHist[Month],0,MONTH)) will give you same result as your AVERAGE('Table'[Column1])

      • nele's avatar
        nele
        Frequent Visitor
        5 years ago

        Is it possible to just use the last period in the DAX? I don't want to use a filter. Only the evolution from last month until this month is relevant.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi nele ,

    You can update the formula of your measure [Development] as below:

    Development =
VAR _lastperiod =
    CALCULATE ( MAX ( PSScoreHist[Period] ), ALLSELECTED ( PSScoreHist ) )
VAR _preperiod =
    CALCULATE (
        MAX ( PSScoreHist[Period] ),
        FILTER ( ALLSELECTED ( PSScoreHist ), PSScoreHist[Period] < _lastperiod )
    )
RETURN
    (
        CALCULATE ( AVERAGE ( PSScoreHist[Score] ), PSScoreHist[Period] = _lastperiod )
            - CALCULATE ( AVERAGE ( PSScoreHist[Score] ), PSScoreHist[Period] = _preperiod )
    ) / 100

    Best Regards

    • nele's avatar
      nele
      Frequent Visitor
      5 years ago

      Hi Yingyinr,

       

      your solution works perfectly! Thank you so much for your support!