Forum Discussion
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 6m data that is throwing me off. In This example Oct-18 gets the sum,Average, % Diff from April-18 to Sept-18.
I have tried using the DATESINPERIOD function but that gives me the results including the current month.
Below is a generic sample. This table is linked to a date table.
| Month | Current Month | Previous Month | Difference | % Diff | Sum Prev 6 M | average Prev 6 M | % Diff past 6m |
| Mar-18 | 1,000,000 | ||||||
| Apr-18 | 1,300,000 | 1,000,000 | 300,000 | 30% | |||
| May-18 | 1,400,000 | 1,300,000 | 100,000 | 8% | |||
| Jun-18 | 1,100,000 | 1,400,000 | (300,000) | -21% | |||
| Jul-18 | 1,100,000 | 1,100,000 | - | 0% | |||
| Aug-18 | 1,000,000 | 1,100,000 | (100,000) | -9% | |||
| Sep-18 | 950,000 | 1,000,000 | (50,000) | -5% | |||
| Oct-18 | 1,000,000 | 950,000 | 50,000 | 5% | 6,850,000 | 1,141,667 | 0.36% |
| Nov-18 | 800,000 | 1,000,000 | (200,000) | -20% | 6,550,000 | 1,091,667 | -3.76% |
| Dec-18 | 800,000 | 800,000 | - | 0% | 5,950,000 | 991,667 | -8.38% |
| Jan-19 | 900,000 | 800,000 | 100,000 | 13% | 5,650,000 | 941,667 | -4.80% |
| Feb-19 | 800,000 | 900,000 | (100,000) | -11% | 5,450,000 | 908,333 | -2.72% |
| Mar-19 | 900,000 | 800,000 | 100,000 | 13% | 5,250,000 | 875,000 | -3.06% |
| Apr-19 | 900,000 | 900,000 | - | 0% | 5,200,000 | 866,667 | -0.14% |
| May-19 | 1,000,000 | 900,000 | 100,000 | 11% | 5,100,000 | 850,000 | -1.02% |
| Jun-19 | 1,000,000 | 1,000,000 | - | 0% | 5,300,000 | 883,333 | 4.17% |
| Jul-19 | 1,200,000 | 1,000,000 | 200,000 | 20% | 5,500,000 | 916,667 | 4.17% |
| Aug-19 | 900,000 | 1,200,000 | (300,000) | -25% | 5,800,000 | 966,667 | 5.42% |
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
3 Replies
Jorgast how about using datediff? eg date[date] = dateadd(date, -6, m)
- Previous 6mth = CALCULATE([Current Month], DATEADD('TABLE'[DATE], -6,MONTH))Gives me blank dataPrevious 6mth = CALCULATE([Current Month], DATEADD('DATE TABLE'[DATE], -6,MONTH))Gives me just the Current month from 6 months 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
