Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX: Rolling calculation using 2 inputs from different tables where some values are missing

I'm trying to create a rolling 12 month calculation as a measure. The calculation is a simple division: take a value from TableA and divide it by the corresponding value in TableB. I'm having difficu...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    That is correct. 

     

    - If ValueA and ValueB and both present for the all of the last 12 months, then use all 12 months of data. 

    - If ValueA is present for 9/12 months and ValueB is present for 12/12 months, then use only those 9 months of data that have both values. 

     

    I think this may work for me:

     

    ValueA = SUM(TableA[ValueA])
    ValueB = SUM(TableB[ValueB])
    ValueB (where ValueA not null) = 
CALCULATE(
    [ValueB],
    FILTER(Months,[ValueA]>0)
)
    ValueC (Last 12 Months) = 
CALCULATE(
    DIVIDE(
        [ValueA],
        [ValueB (where ValueA not null)],
        BLANK()
    )
    ,DATESBETWEEN(
        Months[month],
        DATEADD(LASTDATE(Months[month]),-12,MONTH),
        DATEADD(LASTDATE(Months[month]),-1,MONTH)
    )
)

     

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, Anonymous 

Do you mean that if between the current 12 months, if there one month that doesn't have both ValueA and ValueB at the same month, this month will be excluded?

 

Best Regards,

Lin

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    That is correct. 

     

    - If ValueA and ValueB and both present for the all of the last 12 months, then use all 12 months of data. 

    - If ValueA is present for 9/12 months and ValueB is present for 12/12 months, then use only those 9 months of data that have both values. 

     

    I think this may work for me:

     

    ValueA = SUM(TableA[ValueA])
    ValueB = SUM(TableB[ValueB])
    ValueB (where ValueA not null) = 
CALCULATE(
    [ValueB],
    FILTER(Months,[ValueA]>0)
)
    ValueC (Last 12 Months) = 
CALCULATE(
    DIVIDE(
        [ValueA],
        [ValueB (where ValueA not null)],
        BLANK()
    )
    ,DATESBETWEEN(
        Months[month],
        DATEADD(LASTDATE(Months[month]),-12,MONTH),
        DATEADD(LASTDATE(Months[month]),-1,MONTH)
    )
)

     

    • v-lili6-msft's avatar
      v-lili6-msft
      Community Support
      7 years ago

      HI, Anonymous 

      It's pleasant that your problem has been solved, could you please mark the reply as Answered?

       

      Best Regards,

      Lin