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
AvionMarie
Helper II
Helper II

DAX measure for timeline, using date of last entry

Hi, I wonder if anyone can help please..

I'm working with data on storage availability, each location submits their latest values sporadically over time.
I've started off with a measure that provides the latest total storage availability value using the date of last entry from the different companies, however I am trying to plot this value over time so we can establish if storage availability is going up or down.

 

I made an attempt to use TOTALYTD but this is not working, see attached PBIX

Any ideas on a way forward would be hugely appreciated!

1 ACCEPTED SOLUTION

@AvionMarie 

Not sure this is what you are after. In any case, it is getting increasingly convoluted in an unnecessary way because of how you are structuring it.

 

New Measure Using Date Calendar V2 =
SUMX (
    CALCULATETABLE (
        DISTINCT ( DailyReturns[Location] ),
        ALL ( 'Calendar'[Date from Calendar] ),
        ALL ( DailyReturns )
    ),
    VAR currentDate_ =
        SELECTEDVALUE ( DailyReturns[Date] )
    VAR latestEntryTime0_ =
        CALCULATE (
            MAX ( DailyReturns[Completion time] ),
            'Calendar'[Date from Calendar] <= ( currentDate_ )
        )
    VAR latestEntryTime1_ =
        IF (
            ISBLANK ( latestEntryTime0_ ),
            CALCULATE (
                MAX ( DailyReturns[Completion time] ),
                'Calendar'[Date from Calendar] <= ( currentDate_ ),
                //This now efers to the added Calendar table
                ALL ( DailyReturns[Last 10 Days] )
            ),
            latestEntryTime0_
        )
    RETURN
        CALCULATE (
            SUM ( DailyReturns[Value] ),
            DailyReturns[Completion time] = latestEntryTime1_,
            ALL ( 'Calendar'[Date from Calendar] ),
            //This now efers to the added Calendar table
            ALL ( DailyReturns[Last 10 Days] )
        )
)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

10 REPLIES 10
AlB
Super User
Super User

Hi @AvionMarie 

Watch out. Time intelligence functions only work well when you have a complete date table. Here you do not even have one.

Try this measure directly on your chart:

New Measure V2 =
SUMX (
    CALCULATETABLE (
        DISTINCT ( DailyReturns[Location] ),
        ALL ( DailyReturns[Date] )
    ),
    VAR currentDate_ =
        SELECTEDVALUE ( DailyReturns[Date] )
    VAR latestEntryTime_ =
        CALCULATE (
            MAX ( DailyReturns[Completion time] ),
            DailyReturns[Date] <= ( currentDate_ )
        )
    RETURN
        CALCULATE (
            DISTINCT ( DailyReturns[Value] ),
            DailyReturns[Completion time] = latestEntryTime_,
            ALL ( DailyReturns[Date] )
        )
)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

..Also thanks for mentioning about using a complete date table. I have added a calendar table to the model 👍

Hi @AlB ,

 

Awesome stuff - thanks for your reply 😊

 

I have transposed the DAX into my live model and have realised there's something else to be factored in, sorry!.. There are 2 storage types that I'd like to show as a stacked timeline, and when I try to show both types I get the error message "A table of multiple values was supplied where a single value was expected".. So I am guessing this is something to do with the DISTINCT bit - can you add 2 things in there? Maybe I'm on the wrong track.. 

 

I have added a new column in the PBIX file to show some sample storage types.. Your advice would be appreciated!

@AvionMarie 

Sure, it's the distinct if there now is more than one value with the same datetime (and that is the latest one on the location). You can change the DISTINCT() for whatever you want to happen: SUM( ) perhaps? Place the storage type in the legend of the chart.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

@AlB 

Ah I see thanks for explaining, I have changed it to SUM() and it works - thank you.

 

One last thing.. I wanted to add a filter to the the timeline chart to only show the last 10 days, but if nobody has sent in a return in the last 10 days this means their figure is not then included, but I would like this to be summed too.

I have experimented putting in ALL(column name used to filter 'Last 10 days') on the last line of the CALCULATETABLE and then the CALCULATE sections but it's not making a difference - where am I gong wrong?

@AvionMarie 

🤔 I haven't understood what you are trying to do now. Can you provide an example based on the data and show what the expected result would be?

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

@AlB 

Sure, hopefully this PBIX file explains it better and what I'm trying to achieve.
In this version there's a date calendar table which is being used for the chart axis, I have tweaked the measure accoridngly in view of this - called 'New Measure Using Date Calendar'. 

And to help clarify I've added a calculated column label that identifies returns in the last 10 days, this needs to filter the date range in the chart, but not filter the sum in the measure.

@AvionMarie 

Not sure this is what you are after. In any case, it is getting increasingly convoluted in an unnecessary way because of how you are structuring it.

 

New Measure Using Date Calendar V2 =
SUMX (
    CALCULATETABLE (
        DISTINCT ( DailyReturns[Location] ),
        ALL ( 'Calendar'[Date from Calendar] ),
        ALL ( DailyReturns )
    ),
    VAR currentDate_ =
        SELECTEDVALUE ( DailyReturns[Date] )
    VAR latestEntryTime0_ =
        CALCULATE (
            MAX ( DailyReturns[Completion time] ),
            'Calendar'[Date from Calendar] <= ( currentDate_ )
        )
    VAR latestEntryTime1_ =
        IF (
            ISBLANK ( latestEntryTime0_ ),
            CALCULATE (
                MAX ( DailyReturns[Completion time] ),
                'Calendar'[Date from Calendar] <= ( currentDate_ ),
                //This now efers to the added Calendar table
                ALL ( DailyReturns[Last 10 Days] )
            ),
            latestEntryTime0_
        )
    RETURN
        CALCULATE (
            SUM ( DailyReturns[Value] ),
            DailyReturns[Completion time] = latestEntryTime1_,
            ALL ( 'Calendar'[Date from Calendar] ),
            //This now efers to the added Calendar table
            ALL ( DailyReturns[Last 10 Days] )
        )
)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

@AlB 

Thanks, that's worked nicely.

Do you have any pointers on how the structure could be simplified so I can better understand how to make it less convoluted?

Thanks in advance. 

Hi @AvionMarie ,

 

If the method provided by @AlB  has solved your problem, please mark his reply as the answer so that people who may have the same question can get the solution directly. Your contribution is highly appreciated.

 

 

Best Regards,

Icey

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.