Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

weekly data

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

9 Replies

  • ExcelMonke's avatar
    ExcelMonke
    Impactful Individual

    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's avatar
      Anonymous
      Not applicable

      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.

  • ExcelMonke's avatar
    ExcelMonke
    Impactful Individual

    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's avatar
      Anonymous
      Not applicable

      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's avatar
    ExcelMonke
    Impactful Individual

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

    DateWeek Ending (Calculated column)Tubulars
    3/3/20243/9/202478
    3/4/20243/9/202461
    3/5/20243/9/202468
    3/6/20243/9/202473
    3/7/20243/9/202457
    3/8/20243/9/202442
    3/9/20243/9/202473
    3/10/20243/16/202426
    3/11/20243/16/202459
    3/12/20243/16/202472
    3/13/20243/16/202474
    3/14/20243/16/202420

    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's avatar
      Anonymous
      Not applicable

      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's avatar
        ExcelMonke
        Impactful Individual

        okay, did the measure I recommended work for you?

  • ExcelMonke's avatar
    ExcelMonke
    Impactful Individual

    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?