Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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
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
..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!
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
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?
🤔 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
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.
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
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |