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.
I have a projection measure, previously it used to calculate the actual proj month usage + last 2 month usage, i want to make few changes to it. Instead of the last 2 month i want to use last n month usage. The last n month usage should use the following logic-
Can anyone help me with this dax please, i aa not sure how to start it at the 1st place.
Can you please post the measure you are using currently?
Proud to be a Super User!
Paul on Linkedin.
The current measure I use is -
Tot. Act. proj. Mon. Usage = [Act. proj. Mon. Usage]+[Act. Last2 Mon Comp.Usage]
Act. Last2 Mon Comp.Usage = CALCULATE(count('DO Vs Tag'[Procedure Credit Owner]), FILTER('DO Vs Tag','DO Vs Tag'[Last 2 Mon Procedure]=1))
The measure I need to modify is Tot. Act. proj. Mon. Usage.
Hi @Avivek
You could add a month number column in your table and try a measure like below:
Last N Total =
VAR currentMonth = MAX('Table (2)'[Month Number])
VAR minSelectedMonth = MIN('Table (2)'[Month Number])
VAR n = currentMonth - minSelectedMonth
RETURN
IF(n=0,
CALCULATE([Total],FILTER(ALL('Table (2)'),'Table (2)'[Month Number] = currentMonth)),
CALCULATE([Total],FILTER(ALL('Table (2)'),'Table (2)'[Month Number] > minSelectedMonth && 'Table (2)'[Month Number] <= currentMonth))
)
My [Total] measure in this example is a simple measure Total = SUM('Table (2)'[Value]) where you can change to yours.
Hope this post helps.
Best regards,
Jing
@v-jingzhang , thankyou for the response. But when you say Value which value are you referring to. If you refer Act. proj. Mon. Usage as the table the thing is it is a measure. So in that case I think i may not not be able to use Filter All option.
Please let me know if it may work or I may need a new measure all together.
@Avivek, I'm sorry I didn't make it clear to you. I didn't know what your data table is like so I created some sample data to give this example measure. My sample data is like below, so the Value is simply the Value column in this table. You will need to modify the measure according to your data table.
Could you please share some sample data and what you expect it to display in the report? How will you select the months? (By slicer, filter pane or something else?) Thus I will probably be able to help modify the measures accordingly.
Regards,
Jing
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 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |