How to make rolling expression display actual month year ?

[Paulyeo11]

Hi All

I have a rolling 12 month expression working fine :-

_LAST12 = CALCULATE(sum(SALES[sales]),DATESINPERIOD('Date'[Date],MAX(SALES[date]),-12,MONTH))

Now the label = _LAST12 it does not mean any thing to the user. May i know how to make reflect current month year ?

My PBI file

https://www.dropbox.com/s/fhdce21pu6vuwfr/PBT_V01028%20rolling%2012%20mth%20expression%20label%20dis...

Paul

[Icey · Accepted solution]

Hi @Paulyeo11 ,

Please let us know if the replies above are helpful.

If they are, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please give us more details.

Best Regards,

Icey

[amitchandak]

@Paulyeo11 , it seem like you are restricting the measure to current month and tryoing to get last 12 month rolling data for that month

[AlB]

@Paulyeo11 

Yes, becasue the value for _LAST12 in Dec 2019 is a blank. Like I said, I'd need answers to these questions to be able to help:

1. What would the visual have to show exactly?

2. One value for Dec 2020 and one value for Dec 2019?

3. What values exactly in each of those two cases?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Paulyeo11]

Hi AIB

Now i understand after test your script at my actual doc.

1. What would the visual have to show exactly? Answer :- i like to use the stright table to show the rolling sales amount for 12 mth and also the next year rolling 12 month. So meaning the 2 field must be seperated. ( The reasons i use stright table , so that user can export to excel and get the data with need to create pivot table again )

2. One value for Dec 2020 and one value for Dec 2019? Answer :- Yes correct 2 seperate value. and 2 seperate label.

3. What values exactly in each of those two cases? Answer :- One value is for Jan till Dec 2000 another one is Jan till Dec 2019.

Remark :- You have created the 20 Dec , and now i need to focus on create stand alone 19 Dec.

Thak you for your help.

Paul

[AlB]

@Paulyeo11 

I'm not sure I understand correctly. What would the visual have to show exactly? One value for Dec 2020 and one value fro Dec 2019? What values exactly in each of those two cases?

Try this, a measure that should shows the previous 12 months in Dec 2020 and the previous 12 months in Dec 2019

_LAST12_extended V2 =
VAR currentYM_ =
    FORMAT ( TODAY (), "yy mmm" )
VAR currentYM12_ =
    FORMAT ( EDATE ( TODAY (), -12 ), "yy mmm" )
VAR selectedYM_ =
    SELECTEDVALUE ( 'Date'[YrMth] )
RETURN
    IF (
        selectedYM_ IN { currentYM_, currentYM12_ },
        CALCULATE (
            SUM ( SALES[sales] ),
            DATESINPERIOD ( 'Date'[Date], MAX ( SALES[date] ), -12, MONTH )
        )
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Paulyeo11]

Hi AIB 

thank you for sharing , i still get 20 Dec :-

https://www.dropbox.com/s/5bg5a1mm3eh0wb0/PBT_V01028%20rolling%2012%20mth%20expression%20allison%20A...

Above is my PBI file

Paul

[AlB]

@Paulyeo11 

See it all at work in the attached file (Page 2)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @Paulyeo11 

1. Place Date[YrMth] in the columns of the visual

2. Modify the measure  bit  so that it only shows the current (today's) month

_LAST12 V2 =
VAR currentYM_ =
    FORMAT ( TODAY (), "yy mmm" )
VAR selectedYM_ =
    SELECTEDVALUE ( 'Date'[YrMth] )
RETURN
    IF (
        currentYM_ = selectedYM_
            || NOT ISFILTERED ( 'Date'[YrMth] ),
        CALCULATE (
            SUM ( SALES[sales] ),
            DATESINPERIOD ( 'Date'[Date], MAX ( SALES[date] ), -12, MONTH )
        )
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Paulyeo11]

Hi AIB

wow , you are good , this is what i am looking for. 

I try to modify your script for one more field , that is rolling month for next 12 month :-

_LAST24 V2 =

VAR currentYM_ =

FORMAT ( TODAY (), "yy mmm" )

VAR selectedYM_ =

SELECTEDVALUE ( 'Date'[YrMth] )

RETURN

IF (

currentYM_ = selectedYM_

|| NOT ISFILTERED ( 'Date'[YrMth] ),

CALCULATE(

SUM(SALES[sales]),

DATESINPERIOD('Date'[Date],

maxx('Date',

DATEADD('Date'[Date],-12,MONTH)),-12, MONTH)

)

)

My PBI file

https://www.dropbox.com/s/pgyjj5ssu5cc7kf/PBT_V01028%20rolling%2012%20mth%20expression%20label%20dis...

Can you pls share with me how to make it display 19 DEc ?

Paul

[amitchandak]

@Paulyeo11 , You use month name on the column of matrix

or see if dynamic titles can help : https://www.youtube.com/watch?v=XXv13mkjV2Y&feature=youtu.be

[Paulyeo11]

Hi Amit

Thank you for sharing the video , it is not suitable for rolling month expression , as user is not going to selectedvalue.

How ever it can use it for my another application. I can add this feature.

Paul