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 dim table dim_Date and some Fact table. In my Fact table max value for 'date' column is let's say Sep-2018 (2018-09). I need to create a separate measure MeasureFuture which will equal to the last month of the MeasureX starting from the last_month+1 and ending EOY. So that my data grid will be like this:
Year, Month, MeasureX, MeasureFuture
2018, 06, 100,
2018, 07, 120,
2018, 08, 140,
2018, 09, 200,
2018, 10, , 200
2018, 11, , 200
2018, 12, , 200
Please advise.
Hi @Vadim_Drevin,
In your scenario, is this Dim_Date table a normal calendar table? Did you create any relationships between dim_Date and other fact tables? What's the expression of MeasureX?
Please share us some sample data of your original table which we can copy and paste directly and its corresponding expected result. So that we can make some tests and provide more accurate solutions.
Thanks,
Xi Jin.
Hi @v-xjiin-msft,
Sure, let me provide the real case.
dim_Date = CALENDAR( "1/1/2016", "12/31/2019")
dim_Date is related to Fact tables.
MeasureX=
MeasureX = CALCULATE(SUMX(tbl_Workload, [Workload_x_Rate]*[Probability])*100/1000, FILTER(dim_Date, dim_Date[Date]>[MaxDateOfCostOfRevenue]) )
where Workload_x_Rate is another measure (multiplication tbl_Workload[workload] to a column from another table)...
My task it to get the output like this (numbers in red rectangle are drawn in Paint 😞
where 26 is the value of Measure X for the last calculated month (2018-Oct).
Hi @Vadim_Drevin,
In your scenario, to achieve your requirement, the most important point is to get the last value. So check following measure, hope it works for you:
= VAR LastValue = CALCULATE ( [MeasureX], 'dim_date'[month] = MONTH ( MAX ( tbl_Workload[Date] ) ) && 'dim_date'[Year] = YEAR ( MAX ( tbl_Workload[Date] ) ) ) RETURN IF ( ISBLANK ( [MeasureX] ), LastValue )
By the way, since I don't know your actual situation. Above expression is just my assumption. If you want more accurate suggestions, your pbix file is necessary.
Thanks,
Xi Jin.
@v-xjiin-msft, unfortanaitly this expression doesn't work. The following error appears: "A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.".
Tried to use another filter expression in Calculate function like
... FILTER(dim_Date, dim_Date[Date]=MAX(tbl_Workload[Date])) ...
, but in this case the return equal to [MeasureX] for every month, but not for last month..
This seems to be similar to what you're after: https://community.powerbi.com/t5/Desktop/lastnonblank-in-measure/td-p/310734
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeF, thanks. Tried to compare it with my issue, but couldn't. Can you please suggest how to solve my problem with those functions.
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |