Dax script to retrieve values for previous months does not work

Etienne_Bar — Wed, 26 Jul 2023 03:28:17 GMT

Hello,

On a given date, I need to retrieve sales for the month corresponding to this date, sales from the previous month, sales from the previous month and so on.

I have written the following dax code:

Calcul = 
VAR _LastDate =
    CALCULATE(
        MAX   ( Ventes[Date] ),
        ALL (Ventes[Date])
    )
VAR _FinMois = EOMONTH ( _LastDate, 0 )
VAR _DebutMois = EOMONTH ( _LastDate, -1 ) 
VAR _DebutMoisPrec = EOMONTH ( _DebutMois, -1 ) 
VAR _DebutMoisPrec_2 = EOMONTH ( _DebutMoisPrec, -1 ) 
VAR _DebutMoisPrec_3 = EOMONTH ( _DebutMoisPrec_2, -1 ) 
VAR _VentesMois =
    CALCULATE (
        [Total ventes],
        FILTER ( Ventes, Ventes[Date] > _DebutMois && Ventes[Date] <= _FinMois )
    )
VAR _VentesMoisPrec =
    CALCULATE (
         [Total ventes],
         FILTER ( Ventes, Ventes[Date] > _DebutMoisPrec && Ventes[Date] <= _DebutMois )
    )
VAR _VentesMoisPrec_2 =
    CALCULATE (
         [Total ventes],
         FILTER ( Ventes, Ventes[Date] > _DebutMoisPrec_2 && Ventes[Date] <= _DebutMoisPrec )
    )
RETURN
"Dernier mois " & _DebutMois & " " & _FinMois & " " & _VentesMois &
"-Mois Prec " & _DebutMoisPrec & " " & _DebutMois & " " & _VentesMoisPrec &
"-Mois Prec 2 " & _DebutMoisPrec_2 & " " & _DebutMoisPrec & " " & _VentesMoisPrec_2

Here is the result :

The result is correct for the total but not for each customer

My problem is that it works well for the current month (data returned by the _SalesMonth variable) but not for previous months (_SalesMonthPrec and _SalesMonthPrec_2 variables).

I don't understand why this works fine for the current month but not for previous months.

Of course, the results currently being returned are for debugging purposes.

Can you help me?

Re: Dax script to retrieve values for previous months does not work

mlsx4 — Wed, 26 Jul 2023 08:47:52 GMT

Hi Etienne_Bar

I will use this for the last month sales:

lastMonthSales =CALCULATE([Sales], PARALLELPERIOD('Calendar'[Date],-1,MONTH))

Re: Dax script to retrieve values for previous months does not work

Etienne_Bar — Wed, 26 Jul 2023 09:52:20 GMT

It also works with PREVIOUSMONTH but your solution is more elegant

Calcul avec PreviousMonth =
    VAR _VentesMois = CALCULATE([Total ventes])
    VAR _VentesMois_1 = CALCULATE([Total ventes] , PREVIOUSMONTH(TableDate[Date]) ) 
    VAR _VentesMois_2 = CALCULATE([Total ventes] , PREVIOUSMONTH(PREVIOUSMONTH(TableDate[Date])) ) 
    VAR _VentesMois_3 = CALCULATE([Total ventes] , PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(TableDate[Date]))) )

Thanks a lot !

topic Re: Dax script to retrieve values for previous months does not work in DAX Commands and Tips

Dax script to retrieve values for previous months does not work

Re: Dax script to retrieve values for previous months does not work

Re: Dax script to retrieve values for previous months does not work