Snapshot data

[Anonymous]

Hi I am trying work on the following data to get weekly data for number of tubulars, this is by  Sum of Tubular Quantity column for Jobs which have an Arrival Date and a Completed Date following the reports end date OR no Completed Date, I have the following logic: 

Number of Tubulars On Site = CALCULATE(
SUM('Job Tracker'[Tubular quantity]),
FILTER(
'Job Tracker',
(
'Job Tracker'[Arrival Date] <> BLANK() &&
(
(
'Job Tracker'[Completed Date] > MAX('Job Tracker'[Week Ending])
|| ISBLANK('Job Tracker'[Completed Date])
)
)
)
)
)+0

however using either a date between slider or date tiles clicking on one of them the number would decrease so for example 05/04/2024, 29/03/2024, 22/03/2024 have 2717 but for example 15/03/2024 has 2759, I applied another measure which worked

weekly tubulars =
VAR CurrentWeek = MAX('Job Tracker'[Week Ending])
RETURN CALCULATE('Job Tracker'[Number of Tubulars On Site], ALLEXCEPT('Job Tracker','Job Tracker'[Week Ending]), 'Job Tracker'[Week Ending]<= (CurrentWeek) )+0

is there a way to merge these 2 mesaures together as 1 meaure and delete the 2 measures I created

thanks

[Anonymous · Accepted solution]

Hi @Anonymous 

You can try the following formula:

weekly tubulars = 
VAR CurrentWeek = MAX('Job Tracker'[Week Ending])
RETURN
CALCULATE (
    SUM ( 'Job Tracker'[Tubular quantity] ),
    'Job Tracker'[Week Ending] <= CurrentWeek,
    FILTER (
        ALLEXCEPT ( 'Job Tracker', 'Job Tracker'[Week Ending] ),
        'Job Tracker'[Arrival Date] <> BLANK ()
            && 'Job Tracker'[Completed Date] > CurrentWeek
            || 'Job Tracker'[Completed Date] = BLANK ()
    )
)

If there is still a problem, please provide detailed sample data and the results you are hoping for: How to provide sample data in the Power BI Forum - Microsoft Fabric Community . We can better understand the problem and help you. Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.  

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

perfect that works thanks