Forum Discussion
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
9 Replies
- ExcelMonkeImpactful 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]))- AnonymousNot 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.
- ExcelMonkeImpactful 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) )- AnonymousNot 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
- ExcelMonkeImpactful Individual
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
- AnonymousNot 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
- ExcelMonkeImpactful Individual
okay, did the measure I recommended work for you?
- ExcelMonkeImpactful 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?