How to get the value of same period of last year, the period is not contiguous?

[Anonymous]

Hi all,

I have a column called "Year-Month", and what I need to do is :

1. When I choose the 2019-01, the measure shows sum(value) of 2018-01

2. When I choose the 2019-01 and 2019-02, the measure shows sum(value) of 2018-01 and 2018-02

3. When I choose the 2019-01 and 2019-03, the measure shows sum(value) of 2018-01 and 2018-03

I think it's easy to do the 1. and 2., the problem is 3.

What I did now:

1. Create a measure:

measure1 = sum(value)

2. Create measure 2 :

measure 2 = CALCULATE([measure],FILTER(ALL([Year-Month]),[Year-Month] in {"""" & SUBSTITUTE(CONCATENATEX(DISTINCT([Year-Month]),[Year-Month],""","""),YEAR(TODAY()),YEAR(TODAY())-1) & """"}))

It doesn't work. I don't know why,  could you please help?

Thanks.

Aiolos Zhao

[amitchandak]

If you can month Start or month-end in the table you have month year

you can easily get it using

Var _endDate= Maxx(ALLSELECTED('Date'[Date Filer]),DATEADD(ENDOFMONTH('Date'[Date Filer]),-12,MONTH)))
Var   _start_date= minx(ALLSELECTED('Date'[Date Filer]),DATEADD(STARTOFMONTH('Date'[Date Filer]),-12,MONTH))


return
calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _start_date && Sales[Sales Date] <= _endDate)

[Anonymous]

I can't use the start and end method, please see my request.

When I choose 2019-01 and 2019-03, what I want to show is sum(value) of 2018-01 and 2018-03, no 2018-02.

That's why I didn't find a way to solve it.

Aiolos Zhao

[Anonymous]

If the column is in date format, try the following formula:

Measure2 = CALCULATE(

                                              [Measure],

                                              SAMEPERIODLASTYEAR(TableName[Date])

                            )

[Anonymous]

I have tried the SAMEPERIODYEAR function, but the function can't support the un-continuous selections.

Thanks.

Aiolos Zhao

[amitchandak]

Try

Last year same months Sales = ( 
var _sel_val = SELECTEDVALUE('Date'[Format Month])
var _sel_val_1=SUBSTITUTE(_sel_val,RIGHT(_sel_val,4),(RIGHT(_sel_val,4)-1)&"")
return
calculate(sum(Sales[Sales Amount]),'Date'[Format Month] = _sel_val_1)
)

You need to use left and 4 as you have year at start

[Anonymous]

Thanks for reply, I have tried it, but it seems doesn't work when I select 01 and 03.

By the way, if I only choose 01, it works.

My measure:

Measure = ( 
var _sel_val = SELECTEDVALUE([Year-Month])
var _sel_val_1=SUBSTITUTE(_sel_val,LEFT(_sel_val,4),(LEFT(_sel_val,4)-1)&"")
return
calculate([Amount],[Year-Month] = _sel_val_1)
)

Aiolos Zhao

[amitchandak]

It worked for me for multiple months. The only diff is that I have from the Date table, marked as a date table in Power BI.

Try adding an all filter

Last year same months Sales = ( 
var _sel_val = SELECTEDVALUE('Date'[Format Month])
var _sel_val_1=SUBSTITUTE(_sel_val,RIGHT(_sel_val,4),(RIGHT(_sel_val,4)-1)&"")
return
calculate(sum(Sales[Sales Amount]),all(Sales[Sales Date]),'Date'[Format Month] = _sel_val_1)
)

[Anonymous]

Hi @amitchandak ,

Really thanks for your sample, if I add the Year-Month as dimension, it also works for me.

But the problem is no total.

How do I sum the total of this measure?

I try to use sumx(selectcolumns.....),..)

But it doesn't work.

Could you please help with that?

Thanks.

Aiolos Zhao

[Anonymous · Accepted solution]

@Anonymous  - 

I believe the reason why the summation of the table was missing (as well as the reason why the sample provided by amitchandak works when an additional date column was added) was that SELECTEDVALUE returns the month value only when a distinct value is selected - 

see the documentation here: https://docs.microsoft.com/en-us/dax/selectedvalue-function

In your case where multiple months are applied, SELECTEDVALUE('Date'[Format Month]) returns BLANK() and finally ended up with a blank.

I would suggest trying the following (I guess it should work though I haven't tested yet), where I concatenate all fiscal months of the previous year into one string at the beginning and in the Filter expression I checked if the string contains the specific value which we are looking for 

Last year same months Sales = (
var _month_in_selection = CONCATENATEX(
    ADDCOLUMNS(
        SUMMARIZE('Date','Date'[Format Month]),"New Column Name",SUBSTITUTE(_sel_val,RIGHT([Format Month],4),(RIGHT([Format Month],4)-1)&"")
    ),
    [New Column Name],";"
)
Return
    CALCULATE(SUM(Sales[Sales Amount]),all(Sales[Sales Date]),CONTAINSSTRING(_month_in_selection,'Date'[Format Month]))
)

[Anonymous]

Hi @Anonymous ,

That's really cool! It works for me. Really thanks a lot.

The final measure is :

Measure 3 = (
var _month_in_selection = CONCATENATEX(
    ADDCOLUMNS(
        SUMMARIZE([Year-Month]),"New Column Name",SUBSTITUTE([Year-Month],YEAR(TODAY()),YEAR(TODAY()) - 1&"")
    ),[New Column Name],",")
Return
    CALCULATE([Amount],all([Year-Month]),CONTAINSSTRING(_month_in_selection,[Year-Month])
)
)

I change a little for my version : 

Measure = VAR test = CONCATENATEX(DISTINCT([Year-Month]),[Year-Month],",")
            RETURN CALCULATE([Amount],ALL([Year-Month]),CONTAINSSTRING(SUBSTITUTE(test,YEAR(TODAY()),YEAR(TODAY())-1 & ""),[Year-Month]))

I think the amazing part in your idea is using CONTAINSSTRING(_month_in_selection,[Year-Month])

but not CONTAINSSTRING([Year-Month],_month_in_selection)  -- This will not work.

To be honest, I'm not sure why they are different, I think maybe in CONTAINSSTRING(within text, find text) function.

The "within text" is different from "find text". 

And the "find text" can't be a variable or a function result if the result or variable returns more than 1 value.

So we need to use a column to be "find text", but "within text" can be a variable.

Could you please explain it?

Thanks again.

Aiolos Zhao