# Count from Previous week ending

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

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)
)

It’s not -1 it’s -7

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

1 Like

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.

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.

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

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

@Paul.Gerber

Here is the measure to get previous week value.

Rig Data Insights.pbix (4.0 MB)

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?

@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

Rig Data Insights.pbix (4.0 MB)

@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.

@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