weekly data

[Anonymous]

Hi, I was assigned to recreate a spreadsheet entirely in Power BI

What I want to acheive for example is with the number of tubulars toggling a data slider to switch between the numbers for example, 3.5.24 is 2286, 26.4.2024 is 2578

I currently have this measure here

Number of Tubulars On Site =

CALCULATE (

    SUM ( 'Job Tracker'[Tubular quantity] ),

    FILTER (

        'Job Tracker',

        'Job Tracker'[Arrival Date] <> BLANK ()

        &&  'Job Tracker'[Completed Date] = BLANK ()

    )

)+0

right now on a card is shows 2286 which is right but toggle the slider the number decreases and isnt apart of the spreadsheet

not sure what else to do any assistance would be appreciated

kind regards

Sean

[ExcelMonke]

I would need to better understand how the data is organized to be able to provide something more helpful. What does the data table itself look like? Is there a dates column you can reference? 

[ExcelMonke]

Could you better help me understand how your data is structured? Based on how I understand it, it looks something like this:

Date	Week Ending (Calculated column)	Tubulars
3/3/2024	3/9/2024	78
3/4/2024	3/9/2024	61
3/5/2024	3/9/2024	68
3/6/2024	3/9/2024	73
3/7/2024	3/9/2024	57
3/8/2024	3/9/2024	42
3/9/2024	3/9/2024	73
3/10/2024	3/16/2024	26
3/11/2024	3/16/2024	59
3/12/2024	3/16/2024	72
3/13/2024	3/16/2024	74
3/14/2024	3/16/2024	20

If so, you should be able to just enter the Tubular field into the card and it should auto-sum it for you (see screenshot below).

Alternatively, you can also consider the following measure: 

TubularsAmt = 
CALCULATE(SUM(Tubular[Tubulars]),ALLEXCEPT(Tubular,Tubular[Week Ending (Calculated column)]))

Both give the same result

[Anonymous]

The data comes from a sharepoint list which is imported in Power BI

The spreadsheet is just manually entered with the total every Monday, the reason is that the spreadsheet will not be used in future so I was required to recreate the spreadsheet with the results in Power BI

[ExcelMonke]

okay, did the measure I recommended work for you?

[Anonymous]

No It didnt sorry

[ExcelMonke]

I may need a clearer picture of what it looks like in the PBIX to understand exactly what is going on. However, one thing you can try is using variables in your measure; consider the following:

Measure = 
VAR _StartDate = SELECTEDVALUE(Job Tacker[Arrival Date])
VAR _EndDate = SELECTEDVALUE(Job Tracker[Completed Date])

RETURN
CALCULATE(
SUM(Job Tracker[Tubular Quantity]),
DATESBETWEEN(Job Tracker[Date],_StartDate,_EndDate)
)

[Anonymous]

Currently there is a date tile slicer 

the card is below 

what I am trying to accomplish is 

Currently this does not work as clicking the date before 03/05/2024 the card displays 0, the date tile slicer is from a date table and calculated column. 

Week Ending = 'tbl_Date'[Date]- MOD('tbl_Date'[Date]-1,7)+5

[ExcelMonke]

I would need to know more about the toggle you are describing. Is it just the week ending date? If so, you could just create a matrix with the number of tubs on site and put the week ending date in a slicer. You then shouldn't need any calculations.

If your tubs broken out on a dialy basis, then you can probably consider a calculation along the lines of

CALCULATE(FactTable[Tubulars], ALLEXCEPT(DateTable,DateTable[WeekEndingDate]))

[Anonymous]

Currently it is a date tile slicer so what I am trying to acheive is clicking on 03/05/2024  is 2286, 26.4.2024 is 2578, 19.4.2024 is 2578 etc.