Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

 






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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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.

Anonymous
Not applicable

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

 

 






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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.