Comparing 30 day months vs 31 day months.

[RafaelAguirre02]

Hello

I'm trying to compare current Vs previous month balance daily in a matrix (Columns - Days ofmonth, Rows - Stages, Values - Balance).

When the current month has less days than the previous month the Columns display only the days for the current month, causing the previous month's data to disappear.

For example, if i'm comparing November vs October in will only display values until day 30 although October (Prev. Month) has 31 days. i would like to compare day 30 of november with day 30 and day 31 of october.

How can i fix this problem?

[gmsamborn]

Hi @RafaelAguirre02 

Would it make sense to calculate an average daily sales by dividing sales by the number of days in the month?

Then you can repeat the calculation for the previous month and then make comparisons.

I've used this in the past in a similar scenario and the users actually preferred it.

Let me know if this would be an option.

[lbendlin]

You are omitting the fact that the weekday cadence will be different between these months, so comparing day vs day is pretty meaningless anyway.

You would need a disconnected table with a list from 1 to 31 and a measure to achieve your desired result.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[RafaelAguirre02 · Accepted solution]

True, but in this case because of the type of business, the cadence of the weekdays does not affect the value of the daily analysis.

As for the solution I worked with your idea and helped me achieve my goal! For the current month calculation a used the following measure:

Current_Month =

VAR CALC = CALCULATE(SUM(Data[Balance]),filter('Calendar','Calendar'[Day] = MAX(Days[Days])))

Return

IF(

    ISBLANK(CALC),

    CALCULATE(SUM(Data[Balance]),

    LASTDATE(Data[Date])

    ),

    CALC

)

For the comparative month measure i only calculated this past measure but filtered by a inactive secondary calendar table:

Comparative_Month = CALCULATE([Current_Month],ALL(Calendar),

    USERELATIONSHIP(Calendar[Date],Calendar_Inactive[Baseline Date]))

From there i only made the measure to calculate the difference.