Measure for following Week Total

[Anonymous]

Hello,

Reposting this question with sample data.

Came across a lot of post and solutions for dealing with the issue of measure totals.

But, found it difficult to understand any of the solutions as it pertains to my data.

Hopefully with more experience, it will get easier to compare the apples and oranges.

I have the following measure: (AKA: Digital Net Sales as the column heading in the table below)

Digital Sub Count = CALCULATE(sum(projectionfinal[subscriptionsdrawgross]),projectionfinal[datedowdraw] = "Sunday", projectionfinal[subscriptiontype] = "Digital") 

My goal is to create a similar measure, but for the following week.  

I was able to create a measure for this that uses the Digital Sub Count measure, and it worked like expected.

For example, the Digital Net Sales for 1-15-2023 of 54,414 is the correct value for Dig Next Week Net for 1-08-2023, 54,414.

But, there is no total?

Dig Next Week Net = CALCULATE([Digital Sub Count], dimdate[weekendingdate] = SELECTEDVALUE(dimdate[weekendingdate])+7)

What is the best way to either duplicate the Digital Sub Count measure, but for the following week?

Or, update the Dig Next Week Net measure so that it has a total?

Sample Data Set for the Week Ending 1-08-23 and 1-15-23: 

site	product	productsecondary	version	datedraw	datewedraw	datemedraw	dateqedraw	dateyedraw	datedowdraw	datedowcategorydraw	dateflagdraw	subscriptiontype	deliveryschedule	tenureclass	subscriptions	subscriptionsdrawgross	tempstoppctpredicted	volumesource	volumecategory	volumesubcategory	aamcategory1	aamcategory2	aamcategory3	drawadjustmenttype	isbonusdraw	drawnet	copyrate	revenue
AJC	AJC		2022.2023.1.002.006	2023-01-08	2023-01-08	2023-01-31	2023-03-31	2023-12-31	Sunday	Sunday		Digital	ooooooo	Core	31065.8599	31066.83102	0.013997	datawarehouse.fctsubscriptionhistory	Digital	Subscriptions	DIGITAL (Replica)	Home Delivery	ePaper	DRAW	0	31066.83102	0.38	11805.39579
AJC	AJC		2022.2023.1.002.006	2023-01-08	2023-01-08	2023-01-31	2023-03-31	2023-12-31	Sunday	Sunday		Digital	ooooooo	Marginal	15799.7507	15799.7507	0.013997	datawarehouse.fctsubscriptionhistory	Digital	Subscriptions	DIGITAL (Replica)	Home Delivery	ePaper	DRAW	0	15799.7507	0.38	6003.905265
AJC	AJC		2022.2023.1.002.006	2023-01-08	2023-01-08	2023-01-31	2023-03-31	2023-12-31	Sunday	Sunday		Digital	ooooooo	New	6140.144509	6140.144509	0.013997	consumermodel.uploadacquisitionplan	Digital	Subscriptions	DIGITAL (Replica)	Home Delivery	ePaper	DRAW	0	6140.144509	0.35	2149.050578
AJC	AJC		2022.2023.1.002.006	2023-01-08	2023-01-08	2023-01-31	2023-03-31	2023-12-31	Sunday	Sunday		Digital	ooooooo	Marginal	1204.595827	1204.595827	0.013997	consumermodel.uploadacquisitionplan	Digital	Subscriptions	DIGITAL (Replica)	Home Delivery	ePaper	DRAW	0	1204.595827	0.38	457.746414
AJC	AJC		2022.2023.1.002.006	2023-01-15	2023-01-15	2023-01-31	2023-03-31	2023-12-31	Sunday	Sunday		Digital	ooooooo	New	6127.520158	6127.520158	0.018884	consumermodel.uploadacquisitionplan	Digital	Subscriptions	DIGITAL (Replica)	Home Delivery	ePaper	DRAW	0	6127.520158	0.35	2144.632055
AJC	AJC		2022.2023.1.002.006	2023-01-15	2023-01-15	2023-01-31	2023-03-31	2023-12-31	Sunday	Sunday		Digital	ooooooo	Marginal	1666.665182	1666.665182	0.018884	consumermodel.uploadacquisitionplan	Digital	Subscriptions	DIGITAL (Replica)	Home Delivery	ePaper	DRAW	0	1666.665182	0.38	633.332769
AJC	AJC		2022.2023.1.002.006	2023-01-15	2023-01-15	2023-01-31	2023-03-31	2023-12-31	Sunday	Sunday		Digital	ooooooo	Core	31120.08103	31121.05033	0.018884	datawarehouse.fctsubscriptionhistory	Digital	Subscriptions	DIGITAL (Replica)	Home Delivery	ePaper	DRAW	0	31121.05033	0.38	11825.99912
AJC	AJC		2022.2023.1.002.006	2023-01-15	2023-01-15	2023-01-31	2023-03-31	2023-12-31	Sunday	Sunday		Digital	ooooooo	Marginal	15498.94132	15498.94132	0.018884	datawarehouse.fctsubscriptionhistory	Digital	Subscriptions	DIGITAL (Replica)	Home Delivery	ePaper	DRAW	0	15498.94132	0.38	5889.5977

[v-yangliu-msft]

Hi  @Anonymous ,

You can create a Measure, use HASONEVALUE and IF to make judgments, and make sure Total is correct.

Measure =
var _table1=SUMMARIZE('table name', table name[Week End Date],"Value",[Dig Next Week Net])
return
IF(
    HASONEVALUE('table name' [Week End Date] ),
    [Dig Next Week Net],SUMX(_table1,[Value]))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Liu,

Thanks for the suggestion.

Here is my attempt at the measure:

Dig Next Week Net Totals =

VAR _DigNextWeekNet = SUMMARIZE(projectionfinal, projectionfinal[datewedraw], "DigNextWeekNetTotals", [Dig Next Week Net])

RETURN

IF(HASONEVALUE('projectionfinal'[datewedraw]),

[Dig Next Week Net], SUMX(_DigNextWeekNet, [DigNextWeekNetTotals]))

The rows are correct, but unfortunatly, there is still no total.

Did I follow the example correctly?

[nick_potts]

I think you just need to add an 

ALL(dimdate[date])

Statement as your first filter statement.

[Anonymous]

Hi Nick,

Thanks for the help.

I added the suggested filter, but it did not change anything.  Same data with no total.

Dig Next Week Net = CALCULATE([Digital Sub Count], ALL(dimdate[weekendingdate]),        dimdate[weekendingdate] = SELECTEDVALUE(dimdate[weekendingdate])+7)

Is this what you had in mind?