Problem Calculating MOM%

[Anonymous]

Hi guys,

I've been trying to calculate a MOM% variation, but I can't seem to be able to get it to work. I have a facts table where we have customers with payments in different payment methods. I'm looking to make a chart that shows me if they are paying on time (regardless of payment method) on average on Month on Month basis. The idea is to see if certain customers are improving or getting worse. I have used the built in MOM function which works ok overall, however if want to pair it inside a chart with a customer name I get an error saying: Function 'DATEADD' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion.

Here is my code:

Average of Days Since Due MoM% = 
IF(
ISFILTERED('DSD Invoice'[DateColumn]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_MONTH =
CALCULATE(
AVERAGE('DSD Invoice'[Days Since Due]),
DATEADD('DSD Invoice'[DateColumn], -1, MONTH)
)
RETURN
DIVIDE(
AVERAGE('DSD Invoice'[Days Since Due]) - __PREV_MONTH,
__PREV_MONTH
)
)

[dedelman_clng]

DAX has a PREVIOUSMONTH() function that take a date column as an input and returns the previous month's dates.

Ex:

MOM Variance = [Measure] - CALCULATE([Measure], PREVIOUSMONTH(DateTab[Date])

You can then calculate a percentage off of that.

Hope this helps

David

[Anonymous]

Thanks for that @dedelman_clng, unfortunately this method doesn't work, I get an error saying a date column containing duplicate dates was specified in the call to function 'PREVIOUSMONTH'. This is not supported. I have created a dim table and tried using the dates from the dim table, but for some reason that doesn't seem to be working either. Any other suggestions?

[dedelman_clng]

Make sure the dimension date table you created has a 1-to-many relationship to the fact table you are attempting to use in your calculations.  Using the date column from the date table should eliminate the duplicate date problem.

[Anonymous]

@dedelman_clng, yep that is the only option available as per screenshot. The error is still there.

[dedelman_clng]

What is the code for the measure on which you are trying to calculate variance?

[Anonymous]

@dedelman_clng, I've cleared out the code to include only distinct values (I think that was the issue), however the MOM variance now seems to be matching the DaysDiff:

[dedelman_clng]

I'm confused. Your variance measure references [Total Days One], but you are comparing it to DaysDiff ?

What is the code for [Total Days One] ?