Forum Discussion

Transho's avatar
Transho
Frequent Visitor
5 months ago
Solved

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 D...
  • mizan2390's avatar
    mizan2390
    4 months ago

    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

v-hashadapu's avatar
v-hashadapu
Community Support
5 months ago

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's avatar
Transho
Frequent Visitor
5 months ago

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's avatar
    v-hashadapu
    Community Support
    5 months ago

    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
    )