Month to Month Flow Calculation

[Transho]

Asume you have several fact tables which are consistent and having a reporting date variable and a days past due variable.

They are appended and then additional columns are calculated to count the DPD's within buckets of as:

Bucket - 00 : 0 DPD

Bucekt - 01 : 01-30 DPD

Bucket - 02 : 31-60 DPD

etc.

Then functions are generated to calculate the aggregate count per column as:

etc ...

The output of the appendded table would look like :

And the resulting flow rates table woud be:

Hence the ratios are duplicated along all the cells to claculate the flow rate, which is the count of the current month divided by the count of the previous bucket of the previous month.

I need those ratios to be claculated using DAX.

Thanks for helping me.

[mizan2390 · Accepted solution]

hi @Transho 
The reason you are seeing blanks specifically in April, June, September, and November is due to the exact date-shifting mechanics of the DATEADD function interacting with your "End of Month" snapshots. When DATEADD shifts a date back by one month, it attempts to match the exact day number. 

1. When evaluating May, your date is 5/31/2025. DATEADD attempts to shift this back one month to 4/31/2025. Because April only has 30 days, DATEADD automatically adjusts to the last day of the month, returning 4/30/2025. Since you have data on 4/30/2025, the calculation works perfectly.
2. However, when evaluating April, your date is 4/30/2025. DATEADD shifts this back exactly one month to 3/30/2025. Because March does have a 30th day, it does not adjust to the 31st. Since your March data was recorded on 3/31/2025, DAX finds absolutely no data for 3/30/2025, resulting in a blank.

This exact same logic applies to June (evaluating May 30 instead of May 31), September (evaluating Aug 30 instead of Aug 31), and November (evaluating Oct 30 instead of Oct 31).

Can you try one of these DAX to see it works or not?

Flow Bucket - 02 :=
DIVIDE(
    [Count Bucket - 02],
    CALCULATE(
        [Count Bucket - 01],
        PARALLELPERIOD('Calendar'[Date], -1, MONTH)
    )
)

or 

Flow Bucket - 02 :=
DIVIDE(
    [Count Bucket - 02],
    CALCULATE(
        [Count Bucket - 01],
        PREVIOUSMONTH('Calendar'[Date])
    )
)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

[Transho]

Hi mizan2390,

The "PARALLELPERIOD" worked like magic.

Thanks.

[mizan2390]

Great. Happy to know it worked for you

[v-hashadapu]

Hi @Transho , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[Transho]

Hi again,

As i mentioned in my last reply, the formula worked and the ratios are right but for some reason it is skipping few months while the data is all there hence it is still not comprehensive:

Thanks for your concern

[v-hashadapu]

Hi @Transho , can you confirm if you tried my last suggestion please? and if you did, is the result same?

[FBergamaschi]

Hi @Transho 

thanks for explaining again, now it is clearer

I would suggest a final measure

Flow Bucket - 02 = 
DIVIDE ( 
                 [Count Bucket - 02],
                 CALCULATE ( [Count Bucket - 01], DATEADD ( Calendar[Date], -1, MONTH ) )
)

and so forth

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Transho]

Hi ...

I used the same code earlier but used the "Reporting_Date" time dimension varibale in the appended tables and got the below result :

That's why i required the help of the community since i got confused by the strange behavior output.

[v-hashadapu]

Hi @Transho , Thank you for reaching out to the Microsoft Community Forum.

@FBergamaschi ’s DAX is correct, I think the issue is your date context. From what I understand, you are using Reporting_Date from the fact table, so, DATEADD isn’t shifting to the previous month properly, so you get repeated ratios. I suggest you use a proper Calendar table, relate it to your data and use that in the visual. Then this will work correctly:

Flow Bucket - 02 :=
DIVIDE(
[Count Bucket - 02],
CALCULATE(
[Count Bucket - 01],
DATEADD('Calendar'[Date], -1, MONTH)
)
)

[Transho]

Good day v-hashadapu,

You are right, i created the calendar table and i used the "Calendar"[Date] to relate, and that is exactly what i got in the output.

The issue is that the ratios are right but there are missing ratios for specific months as seen in the result table :

[v-hashadapu]

Hi @Transho , Thank you for reaching out to the Microsoft Community Forum.

Blanks appear when there’s no data in the previous month for the denominator bucket, so DAX can’t compute the ratio. If you want to show 0 instead of blanks, please try:

Flow Bucket - 02 :=
DIVIDE(
[Count Bucket - 02],
CALCULATE(
[Count Bucket - 01],
DATEADD('Calendar'[Date], -1, MONTH)
),
0
)

[Transho]

Thank you for the recommendation. I will try my best to prepare a design with samples.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[FBergamaschi]

Hi @Transho 

I am sorry but I am not able to understand what you are asking

the Excel formula showed does not help as there is no visibility of Excel cells

Please can you explain again in detail and show more pictures

Thanks

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Transho]

Thanks for your time you spared.

Let me put it in brief:

After i appended the files i created additional variables ([Bucket - 00],[Bucket - 01],[Bucket - 02] ... etc).

then i used those variables to generate measures (Count Bucket - 00,Count Bucket - 01,Count Bucket - 02 .. .etc).

The first table shows the tabulation of those measures distributed by the date variable for each file.

Now i need to use those variables or measures to create another measures named (Flow Bucket - 00,Flow Bucket - 01,Flow Bucket - 02 ...etc).

Let us calculate the "FLow Bucket - 02" for month Nov2025 : = 2,988(which is the "Count Bucket - 02") divided by 8,688 (which is the "Count Bucket - 01" of month Oct25)

The arrows in the below table shows the new measures required ratio formula :

Hope i explained a little more and thanks again,