topic How do I create a Dax formula with a date value as the denominator? in DAX Commands and Tips

How do I create a Dax formula with a date value as the denominator?

robarbie — Tue, 08 Jun 2021 18:50:44 GMT

I have a requirment where the vendor wants a measure divded by the month number.

"Abs Perc of Error (Demand Forecast) / by the month number Example: Jan Abs Perc of Error (Demand Forecast) / 1, Feb Abs Perc of Error (Demand Forecast) / 2, etc"

When I try to create the DAX Measure the date denominator will not poplulate or if I try to use Quick Measure it wants to add sum

or another aggregation.

This a quick measure:

Demand Forecast divided by MonthNum =

DIVIDE([Demand Forecast], SUM('DimDate'[MonthNum]))

Any guidance would be apprciated!

Re: How do I create a Dax formula with a date value as the denominator?

Nathaniel_C — Tue, 08 Jun 2021 22:48:52 GMT

Hi robarbie , how is your month number column formatted? As a whole number?
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Re: How do I create a Dax formula with a date value as the denominator?

robarbie — Wed, 09 Jun 2021 14:58:15 GMT

Hi Nathaniel,

It is formatted as a whole number.

Thanks,

Rochelle

Re: How do I create a Dax formula with a date value as the denominator?

Nathaniel_C — Wed, 09 Jun 2021 15:54:38 GMT

Hi robarbie , since DAX needs to know which row to use in a table, it won't let you put in a "naked" column because there are multiple values. I typically use MAX(), but most of the time you could use MIN() as well. Since in a typical calculated column, the engine knows what row it is on the MAX() works fine.

Demand Forecast divided by MonthNum =

DIVIDE([Demand Forecast], MAX('DimDate'[MonthNum]))
Try this and let us know if it works. If not, you may need to provide a bit more about your report.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel