Solved: How to get the value of same period of last year, ... - Page 2

AiolosZhao · ‎09-18-2019

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Anonymous · ‎09-19-2019

@AiolosZhao -

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]))
)

View solution in original post

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

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly