Power BI showing blank for Moving month calculation when a data point is blank

[sengupr]

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. 

 Table in Power BIExcel Pivot table

The formula used for 3 Moving Month calculation in power BI is - 

3MM Weight = CALCULATE(SUM('Dummy Data'[Weight]), DATESINPERIOD('Dummy Data'[Date],LASTDATE('Dummy Data'[Date]),-3,MONTH))/3000000

I am showing the numbers in millions, hence the "/3000000" at the end. Any help in replicating the pivot numbers would be highly appreciated.

Regards,

Ranvir.

[v-alq-msft · Accepted 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.

[Anonymous]

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

[amitchandak]

@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.