Forum Discussion
Month to Month Flow Calculation
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.
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.- 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.
- 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
15 Replies
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.- 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.
- 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
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
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.
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)
)
)
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
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,
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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Thank you for the recommendation. I will try my best to prepare a design with samples.
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.
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
Hi Transho , can you confirm if you tried my last suggestion please? and if you did, is the result same?
