Forum Discussion

Jorgast's avatar
Jorgast
Resolver II
6 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
    6 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
6 years ago

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

Jorgast's avatar
Jorgast
Resolver II
6 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
    6 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