Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey guys,
I'm facing an interesting DAX challenge for visualizing the development of our inventory values based on predefined storage time categories.
My data looks something like this (only relevant columns for this problem):
id | date_in | storage_time_days | storage_time_category |
1 | 01.01.2020 | 189 | E |
2 | 04.02.2020 | 155 | D |
3 | 06.03.2020 | 124 | D |
4 | 20.04.2020 | 79 | C |
5 | 20.04.2020 | 79 | C |
6 | 21.04.2020 | 78 | C |
7 | 05.05.2020 | 64 | C |
8 | 06.05.2020 | 63 | C |
9 | 30.06.2020 | 8 | A |
10 | 02.07.2020 | 6 | A |
Columns "storage_time_days" and "storage_category" are calculated columns, referencing the datediff for date_in and today().
So right now, I'm able to show our inventory values by "storage_time_category" for today's date pretty easily.
But since those calculated columns are based on today() function, I cannot show any timeseries for inventory value by "storage_time_category", answering questions like "how did the inventory value for storage_time_category A develop over time?".
After some try and error, I'm kinda stuck and can't find a solution for a new calculated table or a measure, which transforms the today() function into a flexible daterange.
I hope someone here can enlighten me!
Thank you and best regards,
Jano
Solved! Go to Solution.
@Jano - OK, so let's say you have a disconnected table for a calendar and are going to pick a date in a slicer out of that table as your target date. Then you could write a measure like this:
Measure =
VAR __Today = TODAY()
VAR __Date = MAX('Calendar'[Date])
VAR __StorageTime = MAX('Table'[storage_time])
RETURN
__StorageTime - ( __Today - __Date ) * 1.
So essentially you are getting your __StorageTime in context and then just subtracting the number of days between Today and the chosen date.
@Jano - I'm not clear on what you are trying to achieve. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
@Greg_Deckler Thanks for your response.
I couldnt find any related post here.
Im trying to make it more clear:
Column storage_time_days is based on datediff of date_in and today.
I want to calculate my inventory storage time by any given date going back.
For example, storage_time_days are calculated from today() (2020-07-08), but now I want to know, how those storage times were on 2020-07-03.
So my output should look like this:
id | date_in | storage_time_days | storage_time_category |
1 | 01.01.2020 | 184 | E |
2 | 04.02.2020 | 150 | D |
3 | 06.03.2020 | 119 | C |
4 | 20.04.2020 | 74 | C |
5 | 20.04.2020 | 74 | C |
6 | 21.04.2020 | 73 | C |
7 | 05.05.2020 | 59 | B |
8 | 06.05.2020 | 58 | B |
9 | 30.06.2020 | 3 | A |
10 | 02.07.2020 | 1 | A |
Hope, that makes it clearer.
Best regards,
Jano
@Jano - OK, so let's say you have a disconnected table for a calendar and are going to pick a date in a slicer out of that table as your target date. Then you could write a measure like this:
Measure =
VAR __Today = TODAY()
VAR __Date = MAX('Calendar'[Date])
VAR __StorageTime = MAX('Table'[storage_time])
RETURN
__StorageTime - ( __Today - __Date ) * 1.
So essentially you are getting your __StorageTime in context and then just subtracting the number of days between Today and the chosen date.
hi @Jano
First, you should know that:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result in a visual by its row context.
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Second, for your requirement, you could use a measure to get it as below:
Step1:
Create a separate date table as a slicer to switch any date
Step2:
Create a measure like this
storage_time_days =
DATEDIFF ( MAX ( 'Table'[date_in] ), MAX ( 'Separate'[date] ), DAY )
Now drag [id],[date_in] and this measure into a table visual to show the result.
Regards,
Lin
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |