Summing cost for most recent complete week

[harrame]

Hi folks, I'm working on report that includes showing costs by week up to the current date. We want to have a data table that displays the cost in the most recent complete week for a given category. If that category had no costs in the current week, it should show as blank. Since PBI doesn't have "week" included in the date heirarchy, I have a column that labels each week.

Step one is I have a measure to identify the most recent complete week (all 7 days worth of data):

_Current Week = maxx(topn(1,filter(SUMMARIZE(ALLEXCEPT('Data Table', 'Data Table'[SERVICE_DATE]), [SERVICE_WEEK], "Max Service Date", max([SERVICE_DATE]), "Count Service Date", DISTINCTCOUNT([SERVICE_DATE])), [Count Service Date] = 7), [Max Service Date]), [SERVICE_WEEK])

The idea behind this measure is it summarizes the data table by service week and calculates the max service date (for the purpose of ordering the weeks) and calculates the distinct number of days in each week (to determine which weeks are complete). Then, it removes incomplete weeks and takes the first service week according to the max service date value. Since there should only be one row of data at this point, it takes the "max" service week. This measure appears to work correctly.

However, when I move on to my second measure to sum the cost in the current week, it returns nothing:

_Current Week Cost = sumx(filter('Data Table', [SERVICE_WEEK] = [_Current Week]), [COST])

Now, when I replace [_Current Week] in the second measure with the DAX equation for [_Current Week], the summation measure works. However, I would prefer to keep the [_Current Week] measure separate for other purposes and simply reference it in the filter for [_Current Week Cost]. Any thoughts on why my current setup doesn't work?

[harrame · Accepted solution]

I wound up changing the "ALLEXCEPT" statement to "ALL" and removed the exception for Service Date filtering and that did the trick. Unfortunate because I would prefer to keep that filter exception but I can live without it.

[Anonymous]

Well, the explanation of the behaviour is simple. Measures always do context transition. So, under your FILTER the measure does it as well. Hence you get a blank result. You have to first catch your current week into a variable and then use it to filter under FILTER. Easy.

[amitchandak]

@harrame , if you can use week and date table , then with the help of week rank you can get a This week and last week

If you need the current week you can use a week of today or use the slicer

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

Refer : https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[Greg_Deckler]

@harrame Sorry, having trouble following, can you post sample data as text and expected output?

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[harrame]

Here's a mock table that I drew up: 

Category	SERVICE_WEEK	SERVICE_DATE	COST
LABOR	Sep 07 - Sep 13, 2020	9/8/2020	$     5,000.00
MAINTENANCE	Sep 07 - Sep 13, 2020	9/8/2020	$   28,934.00
LABOR	Sep 07 - Sep 13, 2020	9/7/2020	$   23,483.00
MAINTENANCE	Sep 07 - Sep 13, 2020	9/7/2020	$        216.00
LABOR	Aug 31 - Sep 06, 2020	9/6/2020	$ 968,468.00
MAINTENANCE	Aug 31 - Sep 06, 2020	9/6/2020	$     6,546.00
LABOR	Aug 31 - Sep 06, 2020	9/5/2020	$     9,646.00
MAINTENANCE	Aug 31 - Sep 06, 2020	9/5/2020	$        648.00
LABOR	Aug 31 - Sep 06, 2020	9/4/2020	$   64,684.00
MAINTENANCE	Aug 31 - Sep 06, 2020	9/4/2020	$ 684,684.00
LABOR	Aug 31 - Sep 06, 2020	9/3/2020	$     6,843.00
MAINTENANCE	Aug 31 - Sep 06, 2020	9/3/2020	$     6,877.00
LABOR	Aug 31 - Sep 06, 2020	9/2/2020	$     7,898.00
MAINTENANCE	Aug 31 - Sep 06, 2020	9/2/2020	$ 879,615.00
LABOR	Aug 31 - Sep 06, 2020	9/1/2020	$ 854,968.00
MAINTENANCE	Aug 31 - Sep 06, 2020	9/1/2020	$     8,487.00
LABOR	Aug 31 - Sep 06, 2020	8/31/2020	$ 849,681.00
MAINTENANCE	Aug 31 - Sep 06, 2020	8/31/2020	$     6,863.00
LABOR	Aug 24 - Aug 30, 2020	8/30/2020	$     4,654.00
MAINTENANCE	Aug 24 - Aug 30, 2020	8/30/2020	$   54,964.00
LABOR	Aug 24 - Aug 30, 2020	8/29/2020	$     4,684.00
MAINTENANCE	Aug 24 - Aug 30, 2020	8/29/2020	$        484.00
LABOR	Aug 24 - Aug 30, 2020	8/28/2020	$   84,687.00
MAINTENANCE	Aug 24 - Aug 30, 2020	8/28/2020	$ 846,998.00
LABOR	Aug 24 - Aug 30, 2020	8/27/2020	$        486.00
MAINTENANCE	Aug 24 - Aug 30, 2020	8/27/2020	$     8,468.00
LABOR	Aug 24 - Aug 30, 2020	8/26/2020	$     8,468.00
MAINTENANCE	Aug 24 - Aug 30, 2020	8/26/2020	$        848.00
LABOR	Aug 24 - Aug 30, 2020	8/25/2020	$   48,748.00
MAINTENANCE	Aug 24 - Aug 30, 2020	8/25/2020	$     6,388.00
LABOR	Aug 24 - Aug 30, 2020	8/24/2020	$        846.00
MAINTENANCE	Aug 24 - Aug 30, 2020	8/24/2020	$        877.00

As you can see here, the most recent complete week would be Aug 31 - Sep 06, 2020. The goal is the following:

Category	Current Week	Current Week Cost
LABOR	Aug 31 - Sep 06, 2020	$            2,762,188.00
MAINTENANCE	Aug 31 - Sep 06, 2020	$            1,593,720.00
TOTAL	Aug 31 - Sep 06, 2020	$           4,355,908.00

[CNENFRNL]

@harrame It's a bit hard to troubleshoot with a fairly complex measure; I'd calculate it in a simpler way like this,

Lates Week Cost = 
VAR __max_date = MAXX ( ALL ( 'Data Table'[SERVICE_DATE] ), 'Data Table'[SERVICE_DATE] )
VAR __offset = MOD ( WEEKDAY ( __max_date, 2 ), 7 )
//if max_date is Sunday, no offset is needed; or the week starts from last Sunday
VAR __latest_week =
    DATESINPERIOD ( 'Data Table'[SERVICE_DATE], __max_date - __offset, -7, DAY )
RETURN
    CALCULATE( SUM( 'Data Table'[ COST ] ), __latest_week )