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.
Hi,
I am trying to calcualte running total of only consecutive 1's value.
condition:
1. Date range is selected in date slicer. So date range is always dynamic.
2. Running total should be done in ascending order of date and should be done fir individual resources.
3. whenever 0 is encountered running total should again restart for eg. for resource "abc" for 1/5/2020 Billable value is 0. so for 1/6/2020 running total is again calculated from beginning.
Date | Resource | Billable | running total |
1/2/2020 | abc | 1 | 1 |
1/3/2020 | abc | 1 | 2 |
1/4/2020 | abc | 1 | 3 |
1/5/2020 | abc | 0 | 0 |
1/6/2020 | abc | 1 | 1 |
1/7/2020 | abc | 1 | 2 |
1/2/2020 | xyz | 0 | 0 |
1/3/2020 | xyz | 1 | 1 |
1/4/2020 | xyz | 0 | 0 |
1/5/2020 | xyz | 1 | 1 |
1/6/2020 | xyz | 1 | 2 |
1/7/2020 | xyz | 0 | 0 |
Please help if anyone knows the logic.
Regards
Vaishali Rathi
Solved! Go to Solution.
Hi @Vaishali04
Try this, I'm hoping someone will come up with a more efficient version, but for now, at least you have some solution.
Column =
VAR __date = 'Table'[Date]
VAR __allResource = ALLEXCEPT( 'Table', 'Table'[Resource] )
VAR __firstZero =
CALCULATE(
MAX( 'Table'[Date] ),
__allResource,
'Table'[Date] <= __date,
'Table'[Billable] = 0
)
VAR __start =
IF(
ISBLANK( __firstZero ),
CALCULATE(
MIN( 'Table'[Date] ),
__allResource
),
__firstZero
)
RETURN
CALCULATE(
SUM( 'Table'[Billable] ),
FILTER(
ALL( 'Table'[Date] ),
'Table'[Date] >= __start && 'Table'[Date] <= __date
)
)
See the attached for ref as well
Hi @Vaishali04
Ok, I've added ALLSELECTED
Measure =
VAR __date = SELECTEDVALUE( 'Table'[Date] )
VAR __allResource =
CALCULATETABLE(
'Table',
ALLSELECTED( 'Table' ),
VALUES( 'Table'[Resource] )
)
VAR __firstZero =
CALCULATE(
MAX( 'Table'[Date] ),
__allResource,
'Table'[Date] <= __date,
'Table'[Billable] = 0
)
VAR __start =
IF(
ISBLANK( __firstZero ),
CALCULATE(
MIN( 'Table'[Date] ),
__allResource
),
__firstZero
)
RETURN
CALCULATE(
SUM( 'Table'[Billable] ),
FILTER(
ALL( 'Table'[Date] ),
'Table'[Date] >= __start && 'Table'[Date] <= __date
)
)
Hi @Vaishali04
Try this, I'm hoping someone will come up with a more efficient version, but for now, at least you have some solution.
Column =
VAR __date = 'Table'[Date]
VAR __allResource = ALLEXCEPT( 'Table', 'Table'[Resource] )
VAR __firstZero =
CALCULATE(
MAX( 'Table'[Date] ),
__allResource,
'Table'[Date] <= __date,
'Table'[Billable] = 0
)
VAR __start =
IF(
ISBLANK( __firstZero ),
CALCULATE(
MIN( 'Table'[Date] ),
__allResource
),
__firstZero
)
RETURN
CALCULATE(
SUM( 'Table'[Billable] ),
FILTER(
ALL( 'Table'[Date] ),
'Table'[Date] >= __start && 'Table'[Date] <= __date
)
)
See the attached for ref as well
Hi @Vaishali04
No problem, please see the below
Measure =
VAR __date = SELECTEDVALUE( 'Table'[Date] )
VAR __allResource = ALLEXCEPT( 'Table', 'Table'[Resource] )
VAR __firstZero =
CALCULATE(
MAX( 'Table'[Date] ),
__allResource,
'Table'[Date] <= __date,
'Table'[Billable] = 0
)
VAR __start =
IF(
ISBLANK( __firstZero ),
CALCULATE(
MIN( 'Table'[Date] ),
__allResource
),
__firstZero
)
RETURN
CALCULATE(
SUM( 'Table'[Billable] ),
FILTER(
ALL( 'Table'[Date] ),
'Table'[Date] >= __start && 'Table'[Date] <= __date
)
)
Thanku @Mariusz
But I have a concern. In measure _firstzero and _start variable are not getting adjusted with slider date selections at UI.
Regards
Vaishali Rathi
Hi @Vaishali04
Ok, I've added ALLSELECTED
Measure =
VAR __date = SELECTEDVALUE( 'Table'[Date] )
VAR __allResource =
CALCULATETABLE(
'Table',
ALLSELECTED( 'Table' ),
VALUES( 'Table'[Resource] )
)
VAR __firstZero =
CALCULATE(
MAX( 'Table'[Date] ),
__allResource,
'Table'[Date] <= __date,
'Table'[Billable] = 0
)
VAR __start =
IF(
ISBLANK( __firstZero ),
CALCULATE(
MIN( 'Table'[Date] ),
__allResource
),
__firstZero
)
RETURN
CALCULATE(
SUM( 'Table'[Billable] ),
FILTER(
ALL( 'Table'[Date] ),
'Table'[Date] >= __start && 'Table'[Date] <= __date
)
)
Hi Mariusz ,
I am stuck with a similar kind of problem.
I have a table with columns (example dataset Mock_PBI (1).xlsx) - date of Purchase, Product ID, Product Price, and Product Number.
I am working on finding consecutive dates for each Product ID and then sum up the product price for those dates. The result would be something like:
It would be Really great if you can help me with the problem.
Thank You
Regards
Thanku @Mariusz for quick reply. Logic is really awsm.
But I want the logic to work on dynamic date range. Can you please convert your logic in a measure instead of a column. I am a beginner in power bi so finding it hard to convert as of now.
Thanks a ton 🙂
Regards
Vaishali
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
32 | |
30 | |
18 | |
18 |