Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Proud to be a Super User!
Solved! Go to Solution.
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])) )
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)
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
Proud to be a Super User!
If the column is in date format, try the following formula:
I have tried the SAMEPERIODYEAR function, but the function can't support the un-continuous selections.
Thanks.
Aiolos Zhao
Proud to be a Super User!
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
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
Proud to be a Super User!
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) )
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
Proud to be a Super User!
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])) )
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |