Search code examples
powerpivotdaxssas-tabular

DAX Query - EARLIER() - Compare with previous Quarter

I tried to do a comparison in DAX between multiple Quarter.

In our case we use Power Pivot with a flat table as source. (No star schema)

Here is a small example:

Our source looks like this:

Data source

That's what we have now:

Current PivotTable

Here is our expected result:

Expected Result

I tried to do something with EARLIER() and EARLIEST() function, but it is definitively not good. (Like an MDX LAG() function). In our specific case, we don't have a clear date and I'm not sure if I can use a ParallelPeriod function

SumValuePrevious:=CALCULATE
(
  SUM(Data[Value]);
  FILTER(ALL(Data[Quarter]);
  SUMX
   (
           FILTER(Data; EARLIEST(Data[Quarter]) = Data[Quarter] )
           ; 
           [Value]
   )
)
)

But I get the same result:

New Result

Do you have something to help me?
Thank you very much for your help,
Arnaud

Solution

  • The best way to work with dates is to add a date table. then you can use the default date functions

    Previous Quarter:= CALCULATE(SUM(myTable[myColumn]), PREVIOUSQUARTER('Date'[Date]))

    Adding a date table: 

    Date =
ADDCOLUMNS (
CALENDAR (DATE(2000;1;1); DATE(2025;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)

    Mark your date table as date table Replace your column Quarter in your source, with the first date of the quarter. Create a relationship between both tables.