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
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
