HAVE YTD COUNT - NEED TO CALCULATE WEEKLY COUNT FOR EACH EXTRACT DATE

[Bella42]

Hi, 

I work with student enrolment data. Our database and enrolment data cube only has YTD enrol counts for each extract date. In the past I have calculated Weekly counts for each extract date both simply (use Excel formulas current - previous YTD for each extract date) and using VBA to create a data sheet with all weeks.

I CANNOT for the life of me work out how to do this in DAX.

I have the extract date (this is NOT in date format, it is a whole number in format 20210711) - there is an extract every week on a Sunday, the YTD count for each extract date, I have the year. I cannot add columns (greyed out in the cube) but have managed to add a Measure to give the Week Number for each extract date (but DAX keeps stopping me from using this when i want to).

The table below is a subset of the data I am working with (first 10 extract dates, dummy data). The yellow weekly column I just did in Excel using simple minus formula - this is what I want to achieve in DAX. I want to do this for extract dates for every week for 2019-2021. The field I use in 'values' and to sum/calculate is called 'Enrolment'[Total AVETMISS Count].

Please help, I am tearing my hair out! THANKS.

(If you ever feel a bit cocky that you have completely mastered VBA/Excel, take a trip down SQL>Power Query>PBI>DAX lane, you will feel young and dumb again 🙄)

[Anonymous · Accepted solution]

Hm... It so happens that I have already taken the path of SQL>Power Query>PBI>DAX and I'm an expert on all these things. VBA and Excel included 🙂 So, I felt cocky long time ago :)))

Now, to the point.

// I'm giving you my best shot since
// I know nothing about the structure
// of the model and to write efficient DAX
// you have to know the model. You'll have
// to take it as is and change so that it 
// fits your requirements. For instance,
// retrieving the year from an int like
// YYYYMMDD by using FLOOR is not efficient
// but, as I said, I know nothing about
// your model so I had to use only what
// you've shown.

[Weekly Enrols] =
// Get the last visible week date
// in the context. Extract Date must
// be a true integer. Not text.
var CurrentVisibleWeek = MAX( Extract[Extract Date] )
var CurrentVisibleWeekYTD = 
	CALCULATE(
		SELECTEDVALUE( Extract[YTD Enrols] ),
		Extract[Extract Date] = CurrentVisibleWeek,
		ALL( Extract )		
	)
var PrevWeek =
	CALCULATE(
		// Can do MAX since they are all increasing.
		MAX( Extract[Extract Date] ),
		Extract[Extract Date] < CurrentVisibleWeek,
		ALL( Extract )
	)
var PrevWeekYTD =
	CALCULATE(
		SELECTEDVALUE( Extract[YTD Enrols] ),
		Extract[Extract Date] = PrevWeek,
		ALL( Extract )
	)
var IsCurrentWeekBeginningOfYear =
	// This floor retrieves the year
	// from YYYYMMDD
	FLOOR( PrevWeek / 10000, 1 )
		< FLOOR( CurrentVisibleWeek / 10000, 1 )
var Delta = 
	if( IsCurrentWeekBeginningOfYear,
		CurrentVisibleWeekYTD,
		LastVisibleWeekYTD - PrevWeekYTD
	)
return
	Delta

[Bella42]

and I changed my clumsy VALUE(left(Extract Date, 4)) to your eloquent FLOOR(BASEweek/10000,1)...😎

[Anonymous]

I'm glad it's worked for you. Some errors come from the simple fact that we sometimes forget to put in something like ")" or "]". That's frequent and you should always check formulas for their syntactic correctness. You can use www.daxformatter.com for this. However, in my original code there's no line you show and which gives you an error. You must have copied my code with a mistake. The error comes from you, not me 🙂

Your SELECTEDVALUE is used in a wrong context. Secondly, my measure will work with any date. Just drop it onto the visuals and have your dates on the rows...

[Bella42]

I fixed it! I am sure your code was right, just something screwy with using our cube. It works a treat now. Just took out the SELECTEDVALUE and used FILTER. I also had to nail down the year (our database is a comprehensive lesson in how NOT to build a database). Thank you so much for your help. My final code:

Weekly Enrols2 =
// Get the last visible week date
// in the context. Extract Date must
// be a true integer. Not text.
var BASEWeek = MAX('Report Date'[Extract Date])
var BASEWeekYTD =
    CALCULATE([Total AVETMISS Count],     
        FILTER( ALL('Report Date'),[Extract Date] = BASEWeek && 'Report Date'[Reporting Year] = VALUE(LEFT([Extract Date],4))  ))

var PrevWeek =
    CALCULATE(
        // Can do MAX since they are all increasing.
        MAX('Report Date'[Extract Date]),
         'Report Date'[Extract Date] < BASEWeek,
        ALL( 'Report Date')
    )
var PrevWeekYTD =
    CALCULATE([Total AVETMISS Count],     
        FILTER( ALL('Report Date'),[Extract Date] = PrevWeek && 'Report Date'[Reporting Year] = VALUE(LEFT([Extract Date],4))  ))
var IsCurrentWeekBeginningOfYear =
    // This floor retrieves the year
    // from YYYYMMDD
    FLOOR( PrevWeek / 10000, 1 )
        < FLOOR( BASEWeek/ 10000, 1 )
var Delta =
    if( IsCurrentWeekBeginningOfYear,
        BASEWeekYTD,
        BASEWeekYTD - PrevWeekYTD
    )
return
Delta

[Bella42]

Hi Daxer, So I am getting an error:

says 'Parameter is not the correct type' (a common error for me so far). [Total AVETMISS Count] is a measure native to the enrolment cube. It is format #,0. 

To be honest I am not at all sure why SELECTEDVALUE is used here. Can you explain it to me? Like I'm a 5 year old? 🙂

Also...will this give me weekly counts for ALL weeks, so that if I list all the extract dates in a column and put Weekly Enrols in Values it will give me the weekly count for each extract date? Or is it just for the current week, cause I have that already.

I know I said it made perfect sense to me but in the cold light of day... pining for VBA. Thanks for your help...

[Bella42]

Oh, my. So much I don't know. How exciting. I didn't even know you could use variables. Told you I was a newbie. Thank you so much. I haven't had a chance to test it out yet. But I will tomorrow and let you know. It all makes perfect sense (function naming conventions aside). I just wanted to say a pre-emptive THANK YOU. If it works you will replace Cedric Villani as my new hero.