Forum Discussion

Jorgast's avatar
Jorgast
Resolver II
7 years ago
Solved

Getting Historical 6 month

Hello BI Community,   I am trying to get historical 6 month data so i can use it in a measure i am trying to build. I am able to get the previous month, Difference, % Diff, but it is the previous ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Jorgast 

    Just try this formula:

    Previous 6mth = 

CALCULATE([Current Month], FILTER(ALL('DATE TABLE'),DATEDIFF('DATE TABLE'[Date],MAX('DATE TABLE'[Date]),MONTH)>=1&&DATEDIFF('DATE TABLE'[Date],MAX('DATE TABLE'[Date]),MONTH)<=6))

    Best Regards,

    Lin

     

     

vanessafvg's avatar
vanessafvg
Community Champion
7 years ago

Jorgast  how about using datediff?   eg date[date] =  dateadd(date, -6, m)

  • Jorgast's avatar
    Jorgast
    Resolver II
    7 years ago
    Previous 6mth = CALCULATE([Current Month], DATEADD('TABLE'[DATE], -6,MONTH))
    Gives me blank data
    Previous 6mth = CALCULATE([Current Month], DATEADD('DATE TABLE'[DATE], -6,MONTH))
    Gives me just the Current month from 6 months ago
    • v-lili6-msft's avatar
      v-lili6-msft
      Community Support
      7 years ago

      hi, Jorgast 

      Just try this formula:

      Previous 6mth = 

CALCULATE([Current Month], FILTER(ALL('DATE TABLE'),DATEDIFF('DATE TABLE'[Date],MAX('DATE TABLE'[Date]),MONTH)>=1&&DATEDIFF('DATE TABLE'[Date],MAX('DATE TABLE'[Date]),MONTH)<=6))

      Best Regards,

      Lin