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!
I need your help. I have "Measure A", then I created "Measure B" and finally "Measure C". The problem is Measure B. I need that this measure takes the value of the last month available for Measure A, then divide this value by 12 and then asign the result to the whole year.
In the following image I show you how I would like to see it:
The dax is like this:
- Measure A is ok
- Measure B = CALCULATE(DIVIDE([Measure A];12);ALL('Date'[Fiscal Month]);'Date'[Fiscal Month Nr]=7)
- Measure C = CALCULATE([Measure B]*[Month Nr])
The last month available is Fiscal Month Nr = 7. I am filtering manually this data in Measure B by adding the last filter ('Date'[Fiscal Month Nr]=7), but I need this to be dinamic and change to the following month (number 😎 every time I load new data.
I can´t find a way to solve this. I have tried by adding a new column in my Date table to identify the last month with data but it doesn´t work in a calculate function.
Does anyone know what can I do?
Thank you!!
Solved! Go to Solution.
Please try this expression for your measure. This assumes your Fiscal Month Number column is numeric (you could convert that in the formula if text), and you've made a table visual with Fiscal Month Number, Month, and this measure.
Measure C =
VAR maxfiscalmonth =
CALCULATE (
MAX ( Fiscal[FiscalMonthNumber] ),
FILTER (
ALL ( Fiscal[FiscalMonthNumber] ),
NOT ( ISBLANK ( CALCULATE ( [Measure_A] ) ) )
)
)
VAR maxmonthvalue =
CALCULATE (
SUM ( Fiscal[Measure A] ),
ALL ( Fiscal[FiscalMonthNumber] ),
Fiscal[FiscalMonthNumber] = maxfiscalmonth
)
VAR result =
SELECTEDVALUE ( Fiscal[FiscalMonthNumber] ) * maxmonthvalue / 12
RETURN
result
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try this expression for your measure. This assumes your Fiscal Month Number column is numeric (you could convert that in the formula if text), and you've made a table visual with Fiscal Month Number, Month, and this measure.
Measure C =
VAR maxfiscalmonth =
CALCULATE (
MAX ( Fiscal[FiscalMonthNumber] ),
FILTER (
ALL ( Fiscal[FiscalMonthNumber] ),
NOT ( ISBLANK ( CALCULATE ( [Measure_A] ) ) )
)
)
VAR maxmonthvalue =
CALCULATE (
SUM ( Fiscal[Measure A] ),
ALL ( Fiscal[FiscalMonthNumber] ),
Fiscal[FiscalMonthNumber] = maxfiscalmonth
)
VAR result =
SELECTEDVALUE ( Fiscal[FiscalMonthNumber] ) * maxmonthvalue / 12
RETURN
result
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi!
Thank you for your response! I tried this but didn´t work. Here is what I see now ("Measure D" is the one with the new expression):
I didn't have the Month name in my visual, so I didn't add that column to the All() parts. If you replace the two All( ) with this, it should work
ALL ('Date'[Fiscal Month Nr], 'Date'[Month])
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Now it works! Thank you for your help!
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |