Forum Discussion
Table Totals Manipulation
Hi there
I was wondering if any of you can please assist with the query below.
I have calculated a measure that is giving me correct values for months; however, I would like to amend the DAX Formula so the total shows the calculated value for the last month.
Scenario:
| 02. Month-Year | FYEO (Incorrect Total) | FYEO (Goal) |
| Jul-21 | 29,856,591 | 29,856,591 |
| Aug-21 | 30,873,993 | 30,873,993 |
| Sep-21 | 30,469,306 | 30,469,306 |
| Oct-21 | 30,899,305 | 30,899,305 |
| Total | 144,196,758 | 30,899,305 |
Noting that other calculated measures are part of the example below, I would like to use a combination of VAR and SUMMARIZE (table manipulation) to arrive to the goal
FYEO (Incorrect Total) =
VAR MonthstoEOFY =
CALCULATE( MEDIAN ( DATES[02. FY Periods Remaining] ) ,
KEEPFILTERS ( VALUES ( 'DATES'[Date] ) ) )
VAR MonthlyFYEO =
[YTD Actual] + ( [YTD Actual Monthly Average] * MonthstoEOFY )
VAR TotalFYEO = SUMMARIZE ( DATES , DATES[02. Offset - CurMonth] , "Month Total" , MonthlyFYEO )
RETURN
IF( HASONEVALUE ( DATES[02. Offset - CurMonth] ) ,
MonthlyFYEO,
SUMX ( TotalFYEO, [Month Total] ) )
I believe I need to change the last part of the formula SUMX ( TotalFYEO, [Month Total] ) so it references the "Month Total" for the last month but unsure of which formula work best.
I feel I'm getting close 😅
Any ideas would be greatly appreciated.
Cheers
Hi Anonymous
Try this:
FYEO (Incorrect Total) = VAR MonthstoEOFY = CALCULATE ( MEDIAN ( DATES[02. FY Periods Remaining] ), KEEPFILTERS ( VALUES ( 'DATES'[Date] ) ) ) VAR MonthlyFYEO = [YTD Actual] + ( [YTD Actual Monthly Average] * MonthstoEOFY ) VAR TotalFYEO = SUMMARIZE ( DATES, DATES[02. Offset - CurMonth], "Month Total", MonthlyFYEO ) RETURN IF ( HASONEVALUE ( DATES[02. Offset - CurMonth] ), MonthlyFYEO, CALCULATE ( MonthlyFYEO, FILTER ( ALL ( DATES ), DATES[02. Offset - CurMonth] = "Oct-21" ) ) )If the values in DATES[02. Offset - CurMonth] column are in Date format then you can change the last row of the above code to:
FILTER ( ALL ( DATES ), DATES[02. Offset - CurMonth] = MAX(DATES[02. Offset - CurMonth] ))
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
3 Replies
Hi Anonymous
Try this:
FYEO (Incorrect Total) = VAR MonthstoEOFY = CALCULATE ( MEDIAN ( DATES[02. FY Periods Remaining] ), KEEPFILTERS ( VALUES ( 'DATES'[Date] ) ) ) VAR MonthlyFYEO = [YTD Actual] + ( [YTD Actual Monthly Average] * MonthstoEOFY ) VAR TotalFYEO = SUMMARIZE ( DATES, DATES[02. Offset - CurMonth], "Month Total", MonthlyFYEO ) RETURN IF ( HASONEVALUE ( DATES[02. Offset - CurMonth] ), MonthlyFYEO, CALCULATE ( MonthlyFYEO, FILTER ( ALL ( DATES ), DATES[02. Offset - CurMonth] = "Oct-21" ) ) )If the values in DATES[02. Offset - CurMonth] column are in Date format then you can change the last row of the above code to:
FILTER ( ALL ( DATES ), DATES[02. Offset - CurMonth] = MAX(DATES[02. Offset - CurMonth] ))
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/Hi,
I am not sure if I understood your question correctly, but I tried to simplify it like below.
Please check the below picture and the attached pbix file.
Result: =
VAR monthlymeasure =
SUM ( Data[FYEO] )
VAR totalmeasure =
CALCULATE ( SUM ( Data[FYEO] ), Data[Month-Year] = "Oct-21" )
RETURN
IF ( HASONEVALUE ( Data[Month-Year] ), monthlymeasure, totalmeasure )
