Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Measure for following Week Total

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: 

 

siteproductproductsecondaryversiondatedrawdatewedrawdatemedrawdateqedrawdateyedrawdatedowdrawdatedowcategorydrawdateflagdrawsubscriptiontypedeliveryscheduletenureclasssubscriptionssubscriptionsdrawgrosstempstoppctpredictedvolumesourcevolumecategoryvolumesubcategoryaamcategory1aamcategory2aamcategory3drawadjustmenttypeisbonusdrawdrawnetcopyraterevenue
AJCAJC 2022.2023.1.002.0062023-01-082023-01-082023-01-312023-03-312023-12-31SundaySunday DigitaloooooooCore31065.859931066.831020.013997datawarehouse.fctsubscriptionhistoryDigitalSubscriptionsDIGITAL (Replica)Home DeliveryePaperDRAW031066.831020.3811805.39579
AJCAJC 2022.2023.1.002.0062023-01-082023-01-082023-01-312023-03-312023-12-31SundaySunday DigitaloooooooMarginal15799.750715799.75070.013997datawarehouse.fctsubscriptionhistoryDigitalSubscriptionsDIGITAL (Replica)Home DeliveryePaperDRAW015799.75070.386003.905265
AJCAJC 2022.2023.1.002.0062023-01-082023-01-082023-01-312023-03-312023-12-31SundaySunday DigitaloooooooNew6140.1445096140.1445090.013997consumermodel.uploadacquisitionplanDigitalSubscriptionsDIGITAL (Replica)Home DeliveryePaperDRAW06140.1445090.352149.050578
AJCAJC 2022.2023.1.002.0062023-01-082023-01-082023-01-312023-03-312023-12-31SundaySunday DigitaloooooooMarginal1204.5958271204.5958270.013997consumermodel.uploadacquisitionplanDigitalSubscriptionsDIGITAL (Replica)Home DeliveryePaperDRAW01204.5958270.38457.746414
AJCAJC 2022.2023.1.002.0062023-01-152023-01-152023-01-312023-03-312023-12-31SundaySunday DigitaloooooooNew6127.5201586127.5201580.018884consumermodel.uploadacquisitionplanDigitalSubscriptionsDIGITAL (Replica)Home DeliveryePaperDRAW06127.5201580.352144.632055
AJCAJC 2022.2023.1.002.0062023-01-152023-01-152023-01-312023-03-312023-12-31SundaySunday DigitaloooooooMarginal1666.6651821666.6651820.018884consumermodel.uploadacquisitionplanDigitalSubscriptionsDIGITAL (Replica)Home DeliveryePaperDRAW01666.6651820.38633.332769
AJCAJC 2022.2023.1.002.0062023-01-152023-01-152023-01-312023-03-312023-12-31SundaySunday DigitaloooooooCore31120.0810331121.050330.018884datawarehouse.fctsubscriptionhistoryDigitalSubscriptionsDIGITAL (Replica)Home DeliveryePaperDRAW031121.050330.3811825.99912
AJCAJC 2022.2023.1.002.0062023-01-152023-01-152023-01-312023-03-312023-12-31SundaySunday DigitaloooooooMarginal15498.9413215498.941320.018884datawarehouse.fctsubscriptionhistoryDigitalSubscriptionsDIGITAL (Replica)Home DeliveryePaperDRAW015498.941320.385889.5977

 

 
 

 

Need Help

4 Replies

  • nick_potts's avatar
    nick_potts
    New Member
    3 years ago

    I think you just need to add an 

    ALL(dimdate[date])

    Statement as your first filter statement.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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?
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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?