Calculate N-1

[Adeline]

Hello 

I am new to power bi and i would like to calculate the previous year month day for my net value in the same measure so i can filter it by the hierarchy date of my date table. 

The goal is to have for the CA NET: 

- When filter hierarchy date per Year = 2022 : CA N-1 equals CA of last year 

- When filter hierarchy date per month  : CA N-1 equals CA of last month 

- When filter hierarchy date per day  : CA N-1 equals CA of last day

Is it possible to help 

Thank you in advance

[MAwwad]

Yes, it is possible to calculate the previous year/month/day value of a measure in Power BI. You can use the DAX function called SAMEPERIODLASTYEAR, LASTDATE, and DATEADD.

Here's an example DAX formula that calculates the previous year's net value based on the filter context of the date hierarchy:

CA N-1 = VAR CurrentDate = MAX('Date'[Date]) RETURN IF( ISFILTERED('Date'[Year]), CALCULATE([CA], SAMEPERIODLASTYEAR('Date'[Date])), IF( ISFILTERED('Date'[Month]), CALCULATE([CA], DATEADD('Date'[Date], -1, MONTH)), IF( ISFILTERED('Date'[Date]), CALCULATE([CA], LASTDATE(DATEADD('Date'[Date], -1, DAY))) ) ) )

This formula first gets the maximum date value in the filter context using the MAX function and stores it in a variable called CurrentDate.

Then, it checks the filter context of the date hierarchy using the ISFILTERED function. If the filter context is for the year, it uses the SAMEPERIODLASTYEAR function to get the net value for the same period in the previous year.

If the filter context is for the month, it uses the DATEADD function to subtract one month from the current date and get the net value for that month in the previous year.

If the filter context is for the day, it uses the LASTDATE and DATEADD functions to subtract one day from the current date and get the net value for that day in the previous year.

Note that this formula assumes that your date hierarchy is in a single table called 'Date' and that your net value measure is called [CA]. You may need to adjust the formula accordingly based on your specific data model.

[Adeline]

Hello 

Thank you for your answer. 

The probelm is that when i apply the filters of month or day, it is impossible to show the data 

[Fowmy]

@Adeline 

Can you share some sample data with the desired output to have a clear understanding of your question?
Mention whether you want a calculated column or measure.
You can either paste your data in the reply box or save it in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.

[Adeline]

Helloo I need to combine these 3 measures into one only measure . Can you help me plz ? Thank you in advance

Measure 1 =

  VAR VARIABLE = CALCULATE(SUMX(FT_VENTES,FT_VENTES[CA Net TTC]),REMOVEFILTERS(DIM_COMPARABILITE))

  VAR PERIOD_DATE=(

    VAR PERIOD_SELECTED=SELECTEDVALUE(CHOIX_PERIODE[ID])

    VAR PERIOD_YTD_MTD= TREATAS ( SELECTCOLUMNS(DIM_TEMPS,"DATE",DIM_TEMPS[Date]),DIM_COMPARABILITE[Date])

    VAR PERIOD_LTD = TREATAS (DATESINPERIOD(DIM_TEMPS[Date],MAX(DIM_TEMPS[Date]),-12,MONTH),DIM_COMPARABILITE[Date])

    return SWITCH(SELECTEDVALUE(CHOIX_PERIODE[ID]),

                1,CALCULATE(VARIABLE,DATESYTD(PERIOD_YTD_MTD),REMOVEFILTERS(DIM_TEMPS)),

                2,CALCULATE(VARIABLE,DATESMTD(PERIOD_LTD),REMOVEFILTERS(DIM_TEMPS)),

                4,CALCULATE(VARIABLE,PERIOD_LTD,REMOVEFILTERS(DIM_TEMPS)),

               CALCULATE(SUMX(FT_VENTES,FT_VENTES[CA Net TTC]),KEEPFILTERS(PERIOD_YTD_MTD),REMOVEFILTERS(DIM_TEMPS) ))

            )

Measure 2=

            SWITCH(SELECTEDVALUE(CHOIX_LFL[ID]),

            1,CALCULATE(Measure 1,DIM_COMPARABILITE[LFL (V/F)]=1),

            2,CALCULATE(Measure 1_DATE),

            3,CALCULATE(Measure 1,DIM_COMPARABILITE[LFL Budget (V/F)]=1)

        )

 Meausre 3= SWITCH(

    SELECTEDVALUE('CHOIX_CA_HT_TTC'[ID]),

    1,Measure 2/1.2,

    2,Measure 2

  )

return

IF(ISBLANK(Measure 2), 0, Measure 2)