Selectedvalue for slicer date with 2 months lag

[o59393]

Hi all

I have a date slicer and I need to have a column with a 2 month lag from that slicer's date selected.

Example, slicer is December 2019 (format MMMM YYYY).

Then the column should reflect: october 2019.

Currently I have this and I dont know how to get it right:

As seen the column name 2PY date is reflecting December 2019, but should show october 2019.

Thanks in advance!

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i create a table to test:

Then, i create a measure to show selected date's previous two month date:

2PY Date on selected date = 
VAR a =
    MONTH ( SELECTEDVALUE ( 'Table'[Date] ) ) - 2
RETURN
    SWITCH (
        TRUE,
        a > 0, DATE ( YEAR ( SELECTEDVALUE ( 'Table'[Date] ) ), a, DAY ( SELECTEDVALUE ( 'Table'[Date] ) ) ),
        a = 0, DATE ( YEAR ( SELECTEDVALUE ( 'Table'[Date] ) ) - 1, 12, DAY ( SELECTEDVALUE ( 'Table'[Date] ) ) ),
        a = -1, DATE ( YEAR ( SELECTEDVALUE ( 'Table'[Date] ) ) - 1, 11, DAY ( SELECTEDVALUE ( 'Table'[Date] ) ) )
    )

Choose date as a slicer and when you choose one date, it shows:

Best Regards,

Giotto Zhi

[amitchandak · Accepted solution]

If you simply need to be 2 months behind. You can use column to. In this pbix. I create both column and measure example

https://www.dropbox.com/s/e2wkpei0ilvcw3o/DateBefore2Month.pbix?dl=0

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i create a table to test:

Then, i create a measure to show selected date's previous two month date:

2PY Date on selected date = 
VAR a =
    MONTH ( SELECTEDVALUE ( 'Table'[Date] ) ) - 2
RETURN
    SWITCH (
        TRUE,
        a > 0, DATE ( YEAR ( SELECTEDVALUE ( 'Table'[Date] ) ), a, DAY ( SELECTEDVALUE ( 'Table'[Date] ) ) ),
        a = 0, DATE ( YEAR ( SELECTEDVALUE ( 'Table'[Date] ) ) - 1, 12, DAY ( SELECTEDVALUE ( 'Table'[Date] ) ) ),
        a = -1, DATE ( YEAR ( SELECTEDVALUE ( 'Table'[Date] ) ) - 1, 11, DAY ( SELECTEDVALUE ( 'Table'[Date] ) ) )
    )

Choose date as a slicer and when you choose one date, it shows:

Best Regards,

Giotto Zhi

[o59393]

Hi @v-gizhi-msft 

Loos nice! If you dont mind, can you please explain the dax logic (for my learning and future scenarios)

2PY Date on selected date = 
VAR a =
    MONTH ( SELECTEDVALUE ( 'Table'[Date] ) ) - 2 // The -2 is for?
RETURN
    SWITCH (
        TRUE,  // what does the true do?
        a > 0, DATE ( YEAR ( SELECTEDVALUE ( 'Table'[Date] ) ), a, DAY ( SELECTEDVALUE ( 'Table'[Date] ) ) ), // what is this for?
        a = 0, DATE ( YEAR ( SELECTEDVALUE ( 'Table'[Date] ) ) - 1, 12, DAY ( SELECTEDVALUE ( 'Table'[Date] ) ) ), // what is this for?
        a = -1, DATE ( YEAR ( SELECTEDVALUE ( 'Table'[Date] ) ) - 1, 11, DAY ( SELECTEDVALUE ( 'Table'[Date] ) ) ) // what is this for?
    )

I added the questions inside the code.

Thanks a lot. 

[v-gizhi-msft]

Hi,

The '-2' in formula is to calculate the previous two month.

The 'true' means all the formula behind it is one-to-one relationship, as in formula, if a>0 is right, it will return the date behind it.

The three checking formulas are to check the boundary of each year, if one month-2<0, the corresponding year will -1 and the month will become Nov or Dec.

Best Regards,

Giotto Zhi

[amitchandak]

You can use a measure like this. Make sure you have calendar table and that is marked as date

before 2 months = 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 

Why did you use a measure of sales?

In this particular case it's just a date what I need.

Thanks!!

[amitchandak]

before 2 months = CALCULATE(Max(Sales[Sales date]),dateadd('Date'[date],-2,MONTH)) 

In Case if date, take max of date. At line level it will give the same date 2 month before.

[o59393]

Hi

So this will return me a sales value or a date value? Still kinda lost why you are using a numeric measure.

Thanks!!!

[amitchandak · Accepted solution]

If you simply need to be 2 months behind. You can use column to. In this pbix. I create both column and measure example

https://www.dropbox.com/s/e2wkpei0ilvcw3o/DateBefore2Month.pbix?dl=0

[o59393]

looks perfect, one final thing. If I want to get it for the previous year, should i just do a "-365" in the end of the formula? or is there a more elegant dax code.

Thanks  a lot @amitchandak 

[amitchandak · Accepted solution]

before 12 months = CALCULATE(Max(Sales[Sales date]),dateadd('Date'[date],-12,MONTH)) 
before 12 months = CALCULATE(Max(Sales[Sales date]),dateadd('Date'[date],-1,YEAR))