Assign a current target to a dax measure with a 2M lag

[o59393]

Hi all

I want to know if it's possible to have a dax function that returns me a 2020 target for a specific 2019 month.

For example when I select January 2020, the metric calculates the data from Dec 18 to Nov 19, which is ok.

So basically I would like to have for the months of Nov 19, the target defined in 2020. (since I am selecting in the slicer a date from 2020).

I created on power query a step to translate the single targets defined by year and have them split by month. So the datasource only has the 2020 and 2019 targets, not breakdowed by month. So to edit the source wont be an option.

This is the scenario

The column TIR of the above table contains the targets.

The november target from the first table instead of 1.3, should say 1.05

Here is the pbix.

https://1drv.ms/u/s!ApgeWwGTKtFdhiEAYS_xYsVAjKRe?e=gpsMQ1

Thanks!

[v-juanli-msft]

Hi @o59393 

Your file is complex, i make a simple test as below, please check if it meets your needs.

Create a date table which has no relationship with others

date =
ADDCOLUMNS (
    CALENDARAUTO (),
    "year", YEAR ( [Date] ),
    "month", MONTH ( [Date] ),
    "monthno year", FORMAT ( [Date], "yyyy mm" ),
    "month year", FORMAT ( [Date], "mmmm yyyy" )
)

Create measures

Measure =
CALCULATE (
    MAX ( 'Table'[tir] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        FORMAT ( 'Table'[Date], "mmmm yyyy" ) = MAX ( 'date'[month year] )
            && DATEDIFF ( 'Table'[Date], MAX ( 'Table'[Date] ), MONTH ) IN { -1, -2 }
    )
)

Measure 2 = IF([Measure]=BLANK(),MAX([tir]),[Measure])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

You can create a two month ahead measure. Make sure you are using date table.

example

2 Month ahead Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],2,Month))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[o59393]

Hi @amitchandak 

Not exactly what I am looking for. I want to assign a 2020 target to the month of November 2019 and December 2019.

If you select any date of 2020 in the slicer, then for november and december the targets will be switched to the 2020 ones.

Is it possible?

Thanks

[v-juanli-msft]

Hi @o59393 

If there is any privacy data, please hide it in your pbix.

Best Regards
Maggie