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
Jano
Helper III
Helper III

DAX HELP: get over time values of inventory based on predefined storage time categories

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

 

iddate_instorage_time_daysstorage_time_category
101.01.2020189E
204.02.2020155D
306.03.2020124D
420.04.202079C
520.04.202079C
621.04.202078C
705.05.202064C
806.05.202063C
930.06.20208A
1002.07.20206A

 

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

1 ACCEPTED 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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

iddate_instorage_time_daysstorage_time_category
101.01.2020184E
204.02.2020150D
306.03.2020119C
420.04.202074C
520.04.202074C
621.04.202073C
705.05.202059B
806.05.202058B
930.06.20203A
1002.07.20201A

 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.