Hi, @m4xon
Based on your description, and the data provided, I use the following sample data:
First, I used the following DAX to extract the current number of weeks:
Week = RIGHT('Table'[Week of Year],2)
Accumulation =
CALCULATE (
SUM ( 'Table'[Tasks] ),
FILTER (
'Table',
'Table'[Date] <= EARLIER ( 'Table'[Date] )
&& 'Table'[ID] = EARLIER ( 'Table'[ID] )
)
)
Maxtotaltask1 =
CALCULATE (
MAX ( [Accumulation] ),
FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
)
Weekback1 = [Week]rank1 =
RANKX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])),[Week],,DESC,Dense)Sort by column select rank1:
I use the following measure to create a descending curve:
Descending Line =
VAR _week =
SELECTEDVALUE ( 'Table'[Weekback1] )
VAR _be =
CALCULATE (
SUM ( 'Table'[Tasks] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Week] >= _week )
)
RETURN
MAX ( 'Table'[Maxtotaltask1] ) - _beThe results are shown in the figure below:
I have provided the PBIX file used this time below, I hope it can help you.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @m4xon
Thank you very much for your reply. I use the following DAX to find the weekly task:
Perweektask =
VAR _minday =
CALCULATE ( MIN ( 'Table 2'[minday] ) )
VAR _mintasks =
CALCULATE (
MIN ( 'Table'[Tasks] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _minday )
)
VAR _counts =
CALCULATE ( DISTINCTCOUNT ( 'Table'[Week] ), ALLSELECTED ( 'Table' ) )
VAR _perweekdecending =
DIVIDE ( _mintasks, _counts )
RETURN
_perweekdecendingUse the following DAX for the task corresponding to the smallest date:
Mintask =
VAR _minday =
CALCULATE ( MIN ( 'Table 2'[minday] ) )
VAR _mintasks =
CALCULATE (
MIN ( 'Table'[Tasks] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _minday )
)
RETURN
_mintasks
I used the following DAX to get a curve of uniform decline:
Descending Line1 =
VAR _week =
SELECTEDVALUE ( 'Table'[Weekback1] )
VAR _nums =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Weekback1] ),
FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Weekback1] > _week )
) + 1
RETURN
[Mintask] - [Perweektask] * _nums
Here are the results:
I've provided the PBIX file for this time below, and it would be great if I could help you.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Fabric Community Update - January 2025
Find out what's new and trending in the Fabric community.
| User | Count |
|---|---|
| 144 | |
| 87 | |
| 65 | |
| 50 | |
| 45 |
