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.
I am attempting to quantify the redistribution of a backlog of sales orders (cause of some virus or something 😓) If anyone can help me I would be super appreciative.
I have a normal Date Table connected to a normal Shipped Sales Table. (Shipped dates for sales orders)
I used (a direct copy paste) @tondeli 's DAX Date Facts table and Dynamic Date Table to calculate the total number of backlogged orders we accrue for a given date range. (The separate tables allow my users to see their graphs change as they adjust the date slicer)
I want to know how to add the total backlogged orders to the dates that constitute the rest of the year.
Here is a screenshot for clarity.
I am trying to redistribute the Total Backlog over the rest of the year. Any advice from anyone out there?
(I have already tried just adding the Order Backlog to the Adding Test columns. The order backlog retains its Date Context if that is any help.)
Solved! Go to Solution.
Hi @CEllinger
Add three measures
Measure = SUMX(ALLEXCEPT('date','date'[year]),[selected value])
Measure 2 =
CALCULATE (
DISTINCTCOUNT ( 'date'[year monthno] ),
FILTER (
ALLSELECTED ( 'date' ),
'date'[year]
= MAX ( 'date'[year] )
&& 'date'[Date]
> EOMONTH (
MAX ( 'disconnect date'[Date] ),
0
)
)
)
Measure 3 = [Measure]/[Measure 2]
Measure 4 =
IF (
MAX ( 'date'[Date] )
> EOMONTH (
MAX ( 'disconnect date'[Date] ),
0
),
[rest of the year] + [Measure 3]
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @CEllinger
Is this expected?
If it is ok, please see my steps below:
Create a date table without relationship
Add [date] column to a slicer from this "disconnect date" table.
Create measures
selected value =
CALCULATE (
SUM ( 'Table'[order] ),
FILTER (
'Table',
'Table'[date]
>= MIN ( 'disconnect date'[Date] )
&& 'Table'[date]
<= MAX ( 'disconnect date'[Date] )
)
)
all rest valuue =
CALCULATE (
SUM ( 'Table'[order] ),
FILTER (
'Table',
'Table'[date]
< MIN ( 'disconnect date'[Date] )
|| 'Table'[date]
> MAX ( 'disconnect date'[Date] )
)
)
rest of the year =
CALCULATE (
SUM ( 'Table'[order] ),
FILTER (
'Table',
'Table'[date]
> MAX ( 'disconnect date'[Date] )
&& YEAR ( 'Table'[date] )
= YEAR (
MAX ( 'disconnect date'[Date] )
)
)
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for replying @v-juanli-msft !
I have been able to replicate that so far on my own. What I am trying to do is (from your model) take the 'All Selected Value" and spread it evenly across the "Rest of the Year."
So rest of the year should be
Order | Selected | All Rest | Rest of Year | Desired Rest of Year |
1
| 1 | |||
2 | 2 | |||
3 | 3 | |||
4 | 4 | |||
5 | 5 | |||
6 | 6 | |||
7 | 7 | |||
8 | 8 | |||
9 | 9 | |||
10 | 10 | 10 | 10 +13 | |
1 | 1 | 1 | 1+13 | |
2 | 2 | 2 | 2+13 |
So the SUM(Selected) moved to the Date Range of (Rest of Year).
Is that more clear?
Hi @CEllinger
Add three measures
Measure = SUMX(ALLEXCEPT('date','date'[year]),[selected value])
Measure 2 =
CALCULATE (
DISTINCTCOUNT ( 'date'[year monthno] ),
FILTER (
ALLSELECTED ( 'date' ),
'date'[year]
= MAX ( 'date'[year] )
&& 'date'[Date]
> EOMONTH (
MAX ( 'disconnect date'[Date] ),
0
)
)
)
Measure 3 = [Measure]/[Measure 2]
Measure 4 =
IF (
MAX ( 'date'[Date] )
> EOMONTH (
MAX ( 'disconnect date'[Date] ),
0
),
[rest of the year] + [Measure 3]
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.