topic Re: Newbie 1st Question - Moving Annual Trend in DAX Commands and Tips

Newbie 1st Question - Moving Annual Trend

Lucky71 — Fri, 02 Apr 2021 00:25:13 GMT

I'm new to DAX. I'm trying to work it myself, but I'm totally stuck on this one!

My basic MAT DAX formula works with simple data, ie Date & Amt:

MAT =CALCULATE(

SUM ( FactData[Amt]),

DATESINPERIOD(Dates[Date], LASTDATE ( Dates[Date] ), -1, YEAR))

But when I add Rep into my data, the formula no longer works.

date	Rep	Amt
31/01/2015	A	200
28/02/2015	A	375
31/01/2015	B	208
28/02/2015	B	966

After adding Rep, the MAT formula simply returns the monthly figure instead of the Moving annual trend.

Can somebody please help? I've spent far too long trying to figure this out myself 😩

Thank you

Re: Newbie 1st Question - Moving Annual Trend

daxer-almighty — Fri, 02 Apr 2021 01:14:53 GMT

Lucky71

The formula works and works the way it should. It just obeys all the filters that you put on your dimensions. If you want to ignore some dimensions or attributes, then you have to instruct your DAX to do it. Whatever Rep is, if it filters the fact table, the filter is honored when your measure is calculating the aggregation.

Without more information about your model it's not possible to tell you more.

Re: Newbie 1st Question - Moving Annual Trend

Lucky71 — Fri, 02 Apr 2021 01:33:53 GMT

When I filter the data, eg 2016 only, the MAT only work for Dec16, because it is excluding pre 2016 data which is required for Jan-Nov16 MAT values. How do I amend the formula below to calculate all 2016 monthly MATs correctly, e.g Jun 16 MAT = Jul15 to Jun2016 = 14,118.61, not 6,036.23!

MAT =

CALCULATE (

SUM ( FactData[Amt]),

DATESINPERIOD ( FactData[Date], LASTDATE ( FactData[Date] ), -1, YEAR )

)

I thought I could just add ALL(FactData[Date]), but I could not get that to work.

Thank you

Re: Newbie 1st Question - Moving Annual Trend

daxer-almighty — Fri, 02 Apr 2021 08:14:48 GMT

Lucky71

For the time-intel functions to work correctly you have to mark your Dates table as a date table in the model. Have you done it? If not, then right-click the table in the Fields pane and MARK IT AS A DATE TABLE. Then come back and tell us if it's worked. I'm 1000000% sure you have not marked the table.

Re: Newbie 1st Question - Moving Annual Trend

daxer-almighty — Fri, 02 Apr 2021 08:17:02 GMT

Lucky71

By the way, time-intel functions will NOT work with columns from fact tables. Don't even try as you have above... Columns in fact tables should be all HIDDEN. Slicing and dicing is allowed only through dimensions.

Please read this: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

Re: Newbie 1st Question - Moving Annual Trend

Lucky71 — Fri, 02 Apr 2021 08:45:10 GMT

Star Schema being used AND Dates Table already marked as Date table. See below:

Re: Newbie 1st Question - Moving Annual Trend

Lucky71 — Fri, 02 Apr 2021 09:14:22 GMT

After playing around with the model, your post helped identify the problem.

Star Schema was being used AND the Dates table was marked.

The key to finding the problem was "Slicing and dicing is allowed only through dimensions."

The ERROR was the date slicer which was NOT using the date field from the Dates table! It was using the date field per the fact table. Switching the date field filter (to the dates table version) fixed the issue. Thank you.

Re: Newbie 1st Question - Moving Annual Trend

Anonymous — Fri, 02 Apr 2021 09:36:22 GMT

Lucky71

Great you were able to resolve your issue but please remember that if you leave columns of your fact table accessible to the user from the UI of your report, you are asking for trouble. Fact table should always be hidden and only dimensions exposed. A good design is one where fact tables consist only of keys to dimensions and figures (facts) that are aggregated by measures. The one and only exception to the rule above is a degenerate dimension (which must live in the fact table) but it's very rare. If you don't follow this golden rule of dimensional modeling in PBI.... well, you'll be sorry sooner or later. You've been warned.