Count from Previous week ending

DAX DAX Calculations
1

All,

Thank you for reading. I want to return the previous weeks rig count (oil industry) but it just returns N/A. What am I missing? My time selection is from my Dates called Week Ending.

Thanks

image
image1402×782 255 KB

Last Week Rig Count Diff. =
VAR vLastWeekRigCount =
CALCULATE(
SUM ( ‘Rig report by Basin’[Rig Qty]),
FILTER (
ALL ( ‘Rig report by Basin’[Report Date]),
‘Rig report by Basin’[Report Date] = MAX (‘Rig report by Basin’[Report Date]) -1
)
)
RETURN
IF (vLastWeekRigCount = 0, “N/A”,
DIVIDE ( SUM( ‘Rig report by Basin’[Rig Qty]) - vLastWeekRigCount, vLastWeekRigCount, 0)
)

2

Hi @Paul.Gerber

It’s not -1 it’s -7

MAX (‘Rig report by Basin’[Report Date]) -7

1 Like
3

It worked only if I didn’t select any week ending selection in my slicer. If I select December 5th as a week ending I want last week’s numbers to show but it says N/A. I’ll keep working on it.

4

Hi @Paul.Gerber

Try to understand your DAX code… You are using ALL, It always returns previous week number for lastdate.

If you want to consider slicer values, Instead of ALL use ALLSELECTED.

Please share your PBIX file with sample data, It’s easy to understand what is happing in your Data model.

5

That you . please see attached
Rig counts 2021.xlsm (779.9 KB)
Rig Data Insights.pbix (4.0 MB)

6

@Rajesh I have attached the files. Many thanks at looking

7

@Paul.Gerber

Here is the measure to get previous week value.

image
image1403×640 51.3 KB

Rig Data Insights.pbix (4.0 MB)

8

Okay thanks. But when I select on let’s say Nov 28, the return is zero where it should be 10; the difference. Did it work for you?

9

@Paul.Gerber

There you are dividing difference with selected week value.

If you want to show only difference why you use DIVIDE Function.

Nov 21 is 637 and Nov 28 is 636 difference is -1
Nov 14 is 627, If you select Nov 21 the difference is 10

Here is the measure to show difference

image
image1208×523 43.8 KB

image
image1309×642 181 KB

Rig Data Insights.pbix (4.0 MB)

10

@Rajesh I saw the same thing just now. I thought why do I have divide?? Thanks so much for your help. You’re AWESOME!!

My thought in the beginning was to have the % change of rigs per week. that’s where the DIVIDE came into play.

11

@Rajesh Now that you’ve seen my report…any feedback? What more can I show or build on? Also, the VP of Sales gives me the data based on the week ending on a Friday. My Date Table has week starting on a sunday. So when I select it, the week is referring to the past week. It kind of messes you up if you don’t know

Thanks