Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I am really at a loss on this one. My dataset has a reciept date column, I also have a Calendar table to bring in all dates within a month. Bascially what I want to do is make sure there are values for all dates within the year (not just the reciept dates). If there is no value for a specific row, I want it to bring in the last date value.
I tried to do the below and it is ALMOST what I want but not quite.
Solved! Go to Solution.
Hi @Anonymous ,
Assume that the field [Average of Actual] is a fact field not measure, you can create a measure as below. Then create a visual with this setting( X axis: [Date] of table Calender Value: New created measure [Average Actual values]).
Average Actual values =
VAR Curdate =
MAX ( 'Calender'[Date] )
VAR LastNonBlankDate =
CALCULATE (
MAX ( 'Master PPV Table'[Date] ),
FILTER (
ALL ( 'Master PPV Table' ),
'Master PPV Table'[Date] < Curdate
&& NOT ( ISBLANK ( 'Master PPV Table'[Average of Actual] ) )
)
)
RETURN
IF (
ISBLANK ( SUM ( 'Master PPV Table'[Average of Actual] ) ),
CALCULATE (
[Average of Actual],
FILTER (
ALL ( 'Master PPV Table' ),
'Master PPV Table'[Date] = LastNonBlankDate
)
),
SUM ( 'Master PPV Table'[Average of Actual] )
)
If the above one can't get the correct result, please provide some sample data and the formula of measure if it involve any measure.
Best Regards
Rena
Hi @Anonymous ,
Assume that the field [Average of Actual] is a fact field not measure, you can create a measure as below. Then create a visual with this setting( X axis: [Date] of table Calender Value: New created measure [Average Actual values]).
Average Actual values =
VAR Curdate =
MAX ( 'Calender'[Date] )
VAR LastNonBlankDate =
CALCULATE (
MAX ( 'Master PPV Table'[Date] ),
FILTER (
ALL ( 'Master PPV Table' ),
'Master PPV Table'[Date] < Curdate
&& NOT ( ISBLANK ( 'Master PPV Table'[Average of Actual] ) )
)
)
RETURN
IF (
ISBLANK ( SUM ( 'Master PPV Table'[Average of Actual] ) ),
CALCULATE (
[Average of Actual],
FILTER (
ALL ( 'Master PPV Table' ),
'Master PPV Table'[Date] = LastNonBlankDate
)
),
SUM ( 'Master PPV Table'[Average of Actual] )
)
If the above one can't get the correct result, please provide some sample data and the formula of measure if it involve any measure.
Best Regards
Rena
You seem to be reinventing the LASTNONBLANKVALUE() function.
Technically you can do the same in Power Query via Fill..Up.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |