Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Community, I have a data as above for any date that is selected in between load date and delivery date I need to get the respective volume. For example, if the date filter is selected as 02/04/2017 then the result will be 2000. I need to build a model out of the table above.
To do this, I had worked and found a solution but it has some limitations with it. What I did is create two fact tables with
Fact 1: Date, Region, Transport, Load date, Volume
Fact 2: Date, Region, Transport, delivery date, Volume
and
date dimension: date, week number and so on.
Then relationships are given as fact1.date to date dimention.date and fact2.date to date dimention.date
In fact 1: created a running total Laod: Calculate( sum('fact1'[Volume]),Filter(All('Fact1'), 'fact1'[date]<=max('fact1'[Date'])))
In fact 2: Created a running total Delivery: Calculate( sum('fact2'[Volume]),Filter(All('Fact2'), 'fact2'[date]<=max('fact2'[Date'])))
Now
Third measure, with Total required volume: Running total delivery- Running total load
This way I was able to get the required volume for the specific date but If I filter the 'total required volume' with region and Transport or any of it I was not able to get the result right. Is there a better way to make the design right.
Thanks for your support.
Solved! Go to Solution.
Hi @koushikB,
Maybe you could try this formula.
There are two tables. Your data table called "Table1" and a date table "Calendar". They don't have any relationship. The result "ValidSum" will ignore the filters from date but still can be filtered by "Region" or "Transport".
ValidSum = CALCULATE ( SUMX ( Table1, IF ( HASONEVALUE ( 'Calendar'[Date] ), IF ( 'Table1'[Load Date] <= MIN ( 'Calendar'[Date] ) && Table1[Delivery Date] >= MIN ( 'Calendar'[Date] ), 'Table1'[Volume], BLANK () ), BLANK () ) ), ALL ( Table1[Load Date] ), ALL ( Table1[Delivery Date] ) )
Best Regards!
Dale
Hi @koushikB,
Maybe you could try this formula.
There are two tables. Your data table called "Table1" and a date table "Calendar". They don't have any relationship. The result "ValidSum" will ignore the filters from date but still can be filtered by "Region" or "Transport".
ValidSum = CALCULATE ( SUMX ( Table1, IF ( HASONEVALUE ( 'Calendar'[Date] ), IF ( 'Table1'[Load Date] <= MIN ( 'Calendar'[Date] ) && Table1[Delivery Date] >= MIN ( 'Calendar'[Date] ), 'Table1'[Volume], BLANK () ), BLANK () ) ), ALL ( Table1[Load Date] ), ALL ( Table1[Delivery Date] ) )
Best Regards!
Dale
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |