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'm trying to create a measure which will return the previous month of a number of other measures relative to the current period from my dimDate table.
So for example - return Feb-17 when the dimDate is filtered for Mar-17. Is this possible within a switch function or is there a better way to achieve this?
Thanks
Solved! Go to Solution.
you could use dateadd, create a measure and
ie dateadd(date[date],-1, month)
Proud to be a Super User!
@Anonymous something i just leartn this weekend , incase you have any issues if you have a date dimension, make sure that its linked on a date datatype field (not an int as is often the case in date dimensions when one links on the smart key ie 20010101). this is if you are using using date functions
Proud to be a Super User!
you could use dateadd, create a measure and
ie dateadd(date[date],-1, month)
Proud to be a Super User!
But surely that wouldn't work dynamically? It would only work for the previous month relative to now?
@Anonymous true, probably dont quite understand what you asking
probably best to actually demonstrate what you looking for with examples
Proud to be a Super User!
I said in the original post...
So for example - return Feb-17 when the dimDate is filtered for Mar-17.
return the value of a measure or the date? not 100% clear in my personal opinion
but simple to use a filter with dateadd
ie. calculate(measure, filter(date, date[date] = dateadd(date[date],-1, month)
or better
calculate(measure, previousmonth(date[date])
Proud to be a Super User!
I have a period slicer on my report which comes from my date dimension in the format mmm-yy. This is related to my sales fact table. I want to return the previous month's sales relative to that current filter context.
So if I select Apr-17 I want it to return sales for Mar-17. If I select Jan-17 I want it to return the sales for Dec-2016.
Hi @Anonymous @vanessafvg
What vanessafvg wrote in the first place will accommodate what you are asking. Do you want it to be displayed in a table or in a card-visualization (or something similar)?
Summarizing what will work:
Total sales = SUM(Sales)
Sales last month = CALCULATE([Total sales];DATEADD(TimeDim[Date];-1;MONTH))
If you don't think this will work dynamically, can you please elaborate? I can make this work the way you want you it to.
Best
Martin
@Anonymous
and in terms of the visual are you placing the date on the visual or just the value?
Proud to be a Super User!
Just the value, within a table.
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |