Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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% |
Solved! Go to Solution.
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
@Jorgast how about using datediff? eg date[date] = dateadd(date, -6, m)
Proud to be a Super User!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |