Forum Discussion

finzilla's avatar
finzilla
Frequent Visitor
8 years ago

Same Period Last Week

Hello... I'll simplify this by having only five columns within the SalesTransaction table:

 

TransDate (eg. 05/29/2018)

TransTimePeriod (eg. 06:30-07:00)

SaleAmount (eg. 200.00)

ThisWeek ( measure where = 1 when True)

LastWeek (measure where= 1 when True)

 

I also have a Date table.

 

I want to create a visual that shows the following:

 

 

Essentially I need to summarize the SaleAmount for each TransTimePeriod (easy so far), and then compare it for the same period last week (which is where I'm getting stuck).

 

 

 

 

 

Need Help

4 Replies

    • finzilla's avatar
      finzilla
      Frequent Visitor
      8 years ago

      Here is a simplified example of the SalesTransaction table (it's a whole lot more sophisticated but this will do):

       

       

      Date file is just a list of dates.

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi finzilla,

         

        Please check the demo in the attachment. 

        1. I would suggest adding a column in the date table.

        WeekNum = WEEKNUM([Date],2)

        2. Create three measures.

         

        Today =
CALCULATE ( SUM ( Table1[SaleAmount] ), 'Calendar'[Date] = TODAY () )

        LastWeek =
CALCULATE (
    SUM ( Table1[SaleAmount] ),
    'Calendar'[WeekNum]
        = WEEKNUM ( TODAY (), 2 ) - 1
)

        Variance = [Today] - [LastWeek]

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        Best Regards,

        Dale