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'm having issues with creating a measure for estimated end-of-month results - taking actuals up to a selected date and using forecast-data for the remainder of the month.
I've created an example of what I want to do in this Excel-file. I want to replicate the formula there to DAX so I can use it in my report. Hopefully someone in this
Thank you!
Solved! Go to Solution.
Hi @Anonymous,
Please check out the demo in the attachment.
Column = VAR currentDatum = [Datum] RETURN CALCULATE ( SUM ( Table1[Actuals] ), FILTER ( 'Table1', Table1[Datum] <= currentDatum && Table1[Datum] >= ( EOMONTH ( currentDatum, -1 ) + 1 ) ) ) + CALCULATE ( SUM ( Table1[Forecast] ), FILTER ( Table1, Table1[Datum] > currentDatum && Table1[Datum] <= EOMONTH ( currentDatum, 0 ) ) )
Note: your data is random. The result of [Column] here is correct.
Best Regards,
Dale
Hi @Anonymous,
Please check out the demo in the attachment.
Column = VAR currentDatum = [Datum] RETURN CALCULATE ( SUM ( Table1[Actuals] ), FILTER ( 'Table1', Table1[Datum] <= currentDatum && Table1[Datum] >= ( EOMONTH ( currentDatum, -1 ) + 1 ) ) ) + CALCULATE ( SUM ( Table1[Forecast] ), FILTER ( Table1, Table1[Datum] > currentDatum && Table1[Datum] <= EOMONTH ( currentDatum, 0 ) ) )
Note: your data is random. The result of [Column] here is correct.
Best Regards,
Dale
@v-jiascu-msftAny chance you could also formulate the calculation as a measure instead of a calculated column?
Thank you!
Hi @Anonymous,
Try this one, please.
Measure = CALCULATE ( SUM ( Table1[Actuals] ), FILTER ( ALL ( Table1 ), 'Table1'[Datum] <= MIN ( 'Table1'[Datum] ) && 'Table1'[Datum] >= EOMONTH ( MIN ( 'Table1'[Datum] ), -1 ) + 1 ) ) + CALCULATE ( SUM ( Table1[Forecast] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Datum] > MIN ( 'Table1'[Datum] ) && 'Table1'[Datum] <= EOMONTH ( MIN ( 'Table1'[Datum] ), 0 ) ) )
Best Regards,
Dale
I would like to apply a filter every month like
Ie, For example if I select May month, It should display like this for me =
Thank you for the quick reply!
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 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |