DAX formula to calculate different calculation

[Anonymous]

Hi everyone, 
I am reaching out to experts and peers here to get an idea and hopefully a solution. My environment is SSAS Tabular 2016 Enterprise Ediiton, and my tabular model has two tables, for simplicity.  Power BI and Excel will be our front end visualisation tool. In the future, we will be looking at using AAS/Premium.

1. Fact: 
   • Injury Count
   • Reporting Date Key
2. Dates (Date dim)
   1. Date Key
   2. Day,
   3. Week
   4. Month Name
   5. Year
   6. Month Indexer (to sort Month)

 My business users will report Injury Counts for day, and week. For daily counts they want a SUM of all counts. But for weekly counts, they want the count on the last day of the week. That's because, the weekly count represents how many injuries were there at the end of the week showing the measures taken. The report should also enable drill down from Week to Day. 

I am looking at following options

1 - Have two different tables in my model. Week and Date. Both of them will have Date keys so  I should be able to drill through from Week to Date using Date (Date Ke).

2 - Keep one table with different measures for Day and Week calcs. I have about 18 measures right now, and they are expected grow to about 50+. Maintenance wil be a bit of pain. Downside of this approach, perhaps, is that there won't be any drilldown or drillthrough, I haven't tried it. 

3 - Write DAX measure to handle day and week in the same measure. for daily calculation, do a SUM and for weekly calc, pick up the last day of week's number. I have no idea how will I write it, at this stage :).

I am attaching a sample dataset along with sample output on the first page. 

Data and output sample 
Thanks
Kaz 

[Anonymous]

// All columns in the fact table must be hidden.
// Only measures are allowed in fact tables.
// Slicing and dicing is done only through dimensions.
// Keys are always hidden and helper fields as well.
// There is a 1:* from the Dates table to the FactTable
// and filtering is one-way only.
// The Dates table must be marked THE date table in the model
// and the field holding a date is Day. I understand that DateKey is
// an integer.

[Injury Counts] =
var __oneDayVisible = HASONEVALUE( Dates[DateKey] )
var __oneWeekVisible =
	HASONEVALUE( Dates[Week] )
	&& COUNTROWS( Dates ) = 7
var __sumOfCounts = sum( FactTable[InjuryCount] )
var __countForLastDay =
	CALCULATE(
		sum( FactTable[InjuryCount] ),
		LASTDATE( Dates[Day] )
	)
return
	switch( true(),
		__oneDayVisible, __sumOfCounts,
		__oneWeekVisible, __countForLastDay	
	)

Best

D

[Anonymous]

@Anonymous  thanks so much for writing that out. I'll give it a try.

All but two of your pre-reqs are not matched. the date table can't be marked as the date table of model as I have got multiple dates in there. and, the relationship betwen date and fact table is one to many. 

As a side note, I have kept a base date table coming from my data mart, and and have created calculated tables based of the base date table. And, my calculated tables have two hierarchies, financial and calendar period that go from year through to day/date.

[Anonymous]

@Anonymous, you have to have a date table in the model. No sound model can do without it.

https://www.youtube.com/watch?v=78d6mwR8GtA&t=1247s

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

Best
D

[Anonymous]

The file is unreachable. You need to place it on a shored drive that anyone can access. Sharepoint is a bad idea. Please use OneDrive or Google Drive.

Best
D

[Anonymous]

It is actually OneDrive. I have unshared and shared it again. Please use this link.