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.
Achieved Savings Current =
VAR selectedYear =
SELECTEDVALUE ( AuxDates[Year] )
VAR selectedYM =
SELECTEDVALUE ( AuxDates[Year MonthNr] )
VAR selectedDate =
SELECTEDVALUE ( AuxDates[Date] )
VAR currentYear =
SELECTEDVALUE ( Dates[Year] )
VAR currentYM =
SELECTEDVALUE ( Dates[Year MonthNr] )
VAR currentDate =
SELECTEDVALUE ( Dates[Date] )
RETURN
IF (
currentYear = selectedYear,
SWITCH (
TRUE (),
currentYM < selectedYM,
IF (
EOMONTH ( currentDate, 0 ) = currentDate,
VAR totFDLM =
CALCULATE (
[Total Saving],
'Savings Details'[LPD] = EOMONTH ( currentDate, 0 ),
Dates[Year MonthNr] = currentYM,
ALL ( Dates[Date] )
)
VAR totFDLD =
CALCULATE (
[Total Saving],
'Deposit Details'[LPD]
= EOMONTH ( currentDate, 0 ) - 1,
Dates[Year MonthNr] = currentYM,
ALL ( Dates[Date] )
)
RETURN
IF ( ISBLANK ( totFDLM ), totFDLD, totFDLM )
),
currentYM = selectedYM,
IF (
currentDate = selectedDate,
CALCULATE (
[Total Saving],
'Deposit Details'[LPD] = currentDate,
Dates[Year MonthNr] = currentYM,
ALL ( Dates[Date] )
)
)
)
)
I'm using the following part of the above code to get month-end deposits according to 'Deposits Details'[LPD] date columns or if the month-end date is not available, I calculate the day before month-end. But some months even month-end and day before month-end data not available as per the 'Deposits Details'[LPD] column. So, I want to modify this measure to the max date available of the 'Deposits Details'[LPD] date column.
VAR totFDLM =
CALCULATE (
[Total Saving],
'Savings Details'[LPD] = EOMONTH ( currentDate, 0 ),
Dates[Year MonthNr] = currentYM,
ALL ( Dates[Date] )
)
VAR totFDLD =
CALCULATE (
[Total Saving],
'Deposit Details'[LPD]
= EOMONTH ( currentDate, 0 ) - 1,
Dates[Year MonthNr] = currentYM,
ALL ( Dates[Date] )
)
RETURN
IF ( ISBLANK ( totFDLM ), totFDLD, totFDLM )
Solved! Go to Solution.
Hi @Anonymous ,
Create a calculated column [yearmonth] then you could use ALLEXCEPT() function to get the last exit date for each yearmonth. And you could directly get the value of this last exit date.
yearmonth = FORMAT('Table'[date],"YYYYMM")
Measure =
var max_date = CALCULATE(MAX('Table'[date]),ALLEXCEPT('Table','Table'[yearmonth]))
return
CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),max_date='Table'[date]))
Best Regards,
Jay
Hi @Anonymous ,
Create a calculated column [yearmonth] then you could use ALLEXCEPT() function to get the last exit date for each yearmonth. And you could directly get the value of this last exit date.
yearmonth = FORMAT('Table'[date],"YYYYMM")
Measure =
var max_date = CALCULATE(MAX('Table'[date]),ALLEXCEPT('Table','Table'[yearmonth]))
return
CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),max_date='Table'[date]))
Best Regards,
Jay
@Anonymous , Try like
CALCULATE ([Total Saving],filter(ALL ( Dates[Date] ), Dates[Date] =max(Dates[Date])))
CALCULATE (lastnonblankvalue(Dates[date],[Total Saving]),ALLselected( Dates[Date] ))
Also switch all and allselected and check
I think allexcept(Dates,Dates[Year MonthNbt]) can be explored
CALCULATE (lastnonblankvalue(Dates[date],[Total Saving]),allexcept(Dates,Dates[Year MonthNbt]))
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |