Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello there,
I have this table where rows (region), columns (date) and values (earnings) each one come from a different table but obviously relationships are being made.
The thing is I want to show the value 0 (or 0.0) when a 0 value has entered, so it must have already happened because I get the data always for the previous month. But in the next months (august, september and so) instead of having a 0 ocean I'm trying to either show them as blank instead of 0 or not showing them at all and let them appear when the first data of that month enters.
Is there any way of doing it? Or am I being too perfectionist?
Thank you
Solved! Go to Solution.
Hi @Anonymous,
This normally happens when you make a measure show zero when it returns blank.
If you have date column somwhere in your data, you can make a calculated column to determine whether a date is earlier or equal to today's date and use that as a filter. The sample formula below will return TRUE/FALSE
Not Future Date Calc Column =
'Table'[Date] <= TODAY ()
Or you can just filter the measure directly
Filtered Measure =
CALCULATE ( [mymeasure], FILTER ( 'Table', 'Table'[Date] <= TODAY () ) )
Please take note that TODAY is a volatile function which updates when the dataset is refreshed.
If you want to filter only up to the latest date with data
Not Future Date Calc Column =
Table[Date]
<= CALCULATE (
MAX ( 'Table'[Date] ),
FILTER ( ALL ( 'Table' ), 'Table'[Value] <> BLANK () )
)
Filtered Measure =
VAR __lastdate =
FILTER ( ALL ( 'Table' ), 'Table'[Value] <> BLANK () )
RETURN
CALCULATE ( [mymeasure], FILTER ( Table, Table[Date] <= __lastdate ) )
Proud to be a Super User!
You can create a measure like that:
Filter out future months =
IF('Date'[Date]<=TODAY(),1,0)
Then add this measure to the Filter Pane of this visual and select only values 1. All future months should have value 0 and will not be displayed.
'Date'[Date] should be your date field from the Date table.
When I try to write the 'Date'[Date] part, PBI doesn't allow me to. It's like the if clause only let's me reference another measure but not the Date column or table, even if this measure is created in the same date table
Use SELECTEDVALUE('Date'[Date]). That should do the trick.
Hi @Anonymous,
This normally happens when you make a measure show zero when it returns blank.
If you have date column somwhere in your data, you can make a calculated column to determine whether a date is earlier or equal to today's date and use that as a filter. The sample formula below will return TRUE/FALSE
Not Future Date Calc Column =
'Table'[Date] <= TODAY ()
Or you can just filter the measure directly
Filtered Measure =
CALCULATE ( [mymeasure], FILTER ( 'Table', 'Table'[Date] <= TODAY () ) )
Please take note that TODAY is a volatile function which updates when the dataset is refreshed.
If you want to filter only up to the latest date with data
Not Future Date Calc Column =
Table[Date]
<= CALCULATE (
MAX ( 'Table'[Date] ),
FILTER ( ALL ( 'Table' ), 'Table'[Value] <> BLANK () )
)
Filtered Measure =
VAR __lastdate =
FILTER ( ALL ( 'Table' ), 'Table'[Value] <> BLANK () )
RETURN
CALCULATE ( [mymeasure], FILTER ( Table, Table[Date] <= __lastdate ) )
Proud to be a Super User!
That answer was super fast and super precise! No wonder why you are a Super User, thank you very much!
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |