Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I am trying to create a 3 month moving calculation. A simple table - Gender Usage Breakup of a Product over time based on 3 moving months. Some of the months have no respondents for a particular month - for example if you see the 201404 data, Female is blank. If the data point is blank, Power BI is not reporting the 3 moving month for that particular month. See pivot datapoints 201404, 201408 etc. Those data points are missing in power BI table. Even if the data point has no data, I need the Power BI table to show the moving caculation (even if it is based on 2 months instead of 3)
I have attached the powerBI table as well and marked the missing points in yellow. Ideally the 3 moving month data should start from March 2014 - I have kept all the months' calculation for comparison purposes.
The formula used for 3 Moving Month calculation in power BI is -
Solved! Go to Solution.
Hi, @sengupr
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Calendar:
Calendar = CALENDARAUTO()
There is a relationship between two tables. You may create a measure as below.
Result =
CALCULATE(
SUM('Table'[Value]),
DATESINPERIOD(
'Calendar'[Date],
LASTDATE('Calendar'[Date]),
-3,
MONTH
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @sengupr
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Calendar:
Calendar = CALENDARAUTO()
There is a relationship between two tables. You may create a measure as below.
Result =
CALCULATE(
SUM('Table'[Value]),
DATESINPERIOD(
'Calendar'[Date],
LASTDATE('Calendar'[Date]),
-3,
MONTH
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @sengupr ,
1. Can you please share the columns of your reponse data table ?
2. Is it flat like - Month(eg 201501,201502 , etc), Count Female, Count Male
3. If it is so, add a column
IsNonBlankValueFemale = IF (ISBLANK([Count Female]),0,1)
If the value is blank it will be 1 else the value will be 1
4. Similarly create
IsNonBlankValueMale = IF (ISBLANK([Count Male]),0,1)
5. Now for Female 3 month moving average
3MM Female Weight =
VAR CountMonths =
CALCULATE (
SUM ( 'Dummy Data'[IsNonBlankValueFemale] ),
DATESINPERIOD (
'Dummy Data'[Date],
LASTDATE ( 'Dummy Data'[Date] ),
-3,
MONTH
)
)
RETURN
CALCULATE (
SUM ( 'Dummy Data'[Count Female] ),
DATESINPERIOD (
'Dummy Data'[Date],
LASTDATE ( 'Dummy Data'[Date] ),
-3,
MONTH
)
) / ( 1000000 * CountMonths )
What the CountMonths does is to get the number non blank female values in the past 3 month. It will be 0,1,2,3
as the case may be. And this value is used to compute the average.
You may have to tweak the final formula.
6. Similarly develop for 3MM Male Weight,.
7. Add 3MM Male Wright and 3 MM Female Weight to get combined total,.
Check it out and let me know if this worked.
Cheers
CheenuSing
@sengupr ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Try like
3MM Weight = CALCULATE(SUM('Dummy Data'[Weight]), DATESINPERIOD('Dummy Data'[Date],Max('Dummy Data'[Date]),-3,MONTH))/3000000
better try with a date table
3MM Weight = CALCULATE(SUM('Dummy Data'[Weight]), DATESINPERIOD('Date'[Date],Max('DDate'[Date]),-3,MONTH))/3000000
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
User | Count |
---|---|
96 | |
85 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |