cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AllToKnow00
Frequent Visitor

Show data until today in a table

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. 

 

Captura de pantalla 2022-08-17 082624.png

 

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

1 ACCEPTED SOLUTION
danextian
Super User
Super User

Hi @AllToKnow00,

 

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 ) )

 

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

5 REPLIES 5
Ale
Resolver II
Resolver II

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.

AllToKnow00
Frequent Visitor

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.

danextian
Super User
Super User

Hi @AllToKnow00,

 

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 ) )

 

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

That answer was super fast and super precise! No wonder why you are a Super User, thank you very much!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors