cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tmears Member
Member

WTD Verus last WTD

Hi

I am a bit stuck, i want to calcuate WTD number of orders verus the same period last week.  Therfore as today is a wednesday, i want to calcuate the number of orders on Monday, tuesday and Wedensday:  This bit is easy or I have done it:

 

Sales this Week =
CALCULATE (
[Count of Sales History ID],
FILTER (
'Date',
YEAR ( 'Date'[Date] ) = YEAR ( NOW () )
&& WEEKNUM ( 'Date'[Date], 2 ) = WEEKNUM ( NOW (), 2 )))
 
However i want to calcuate the same period last week, ie how many orders did we do on monday, tuesday and wednesday last week. was thinking Parallelperiod but obviously cant use week? Has anyone got any ideas?
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: WTD Verus last WTD

@tmears ,

I attached the pbix below, but here's an overview of what I did. The date table you were using didnt have the all the dates for 2019. I looked at the code but couldnt see how to fix that. So I created a new date table using power query.  You can look at the applied steps and see what I did. I basically needed a way to have a cumulative week number, year, and the day week #.  I added in the Month Name ( and Month to sort the month name) as well. I then created a datekey to match your datekey in the sales table.  Once loaded I made sure to mark that as date table, and related that to your fact table. 

 

Here's the M code, but probably easier to step through

 

let
    Source = Query1(2018, 2019),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Inserted Week of Year" = Table.AddColumn(#"Renamed Columns", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Inserted Year" = Table.AddColumn(#"Inserted Week of Year", "Year", each Date.Year([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Year", {"Year", "Week of Year"}, {{"Count", each _, type table [Date=date, Week of Year=number, Year=number]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Index",{"Index", "Count"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Date"}, {"Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Count",{"Date", "Index"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Index", "WeekNum"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "DateKey", each Text.Combine(
    {
        Text.From( Date.Year([Date]) ),

        Text.PadStart(
            Text.From(
                Date.Month([Date])
            ),
            2,
            "0"
        ),

        Text.PadStart(
            Text.From(
                Date.Day([Date])
            ),
            2,
            "0"
        )
    }
)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"DateKey", type text}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type1", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Month" = Table.AddColumn(#"Inserted Month Name", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Year1" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Year1", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type)
in
    #"Inserted Day of Week"

Now that we have a proper date table, I put Year, Week Number and date in a matrix. Then I started with your Count measure.  Next measure was the week to date:

Week to Date = 
--Set Variables
Var __CurrentWeekNum= MAX( NewDateTable[WeekNum]) 
Var __CurrentDate=MAX( NewDateTable[Date])
return

IF( 
    COUNTROWS(FactSalesHistory)>0,                           --Prevents figures from being projected into future dates
        CALCULATE(
            [COunt of Sales History ID1],
                Filter(
                 ALL( NewDateTable),                        --Remove all the filters from the NewDate Table
                  __CurrentWeekNum = NewDateTable[WeekNum]  --Then only want rows where the weeknumnber = the current weeknumber in the current filter context
                  &&
                  __CurrentDate >= NewDateTable[Date]       --And where the current date is >= the date. So that we get a list of dates for the running total
                )
            )
        )
Prev Week To Date = 
--Set Variables for easier code
VAR __CurrentWeek = Max( NewDateTable[WeekNum])
Var __PrevWeek = __CurrentWeek - 1
Var __CurrentDay=max( NewDateTable[Day of Week])
RETURN

IF( 
    COUNTROWS(FactSalesHistory) >0,
        CALCULATE(
            [COunt of Sales History ID1],
        FILTER(
            ALL( NewDateTable),
            NewDateTable[WeekNum] = __PrevWeek          --Now want all the rows in the date table that equal the previous weeknumber
            &&
            __CurrentDay >=NewDateTable[Day of Week]    --Then only want those day numbers that are equal or > the current day number     
            
        )
    )
)

Final Matrix with Code.png

 

 

View solution in original post

9 REPLIES 9
Super User
Super User

Re: WTD Verus last WTD

Weeks can be a litttle tricky.  The first thing to do is to get a Year Column and WeekNumber column in your date table. If you are using a standard calendar that can be easily done in power query or calculated columns. If you are using a non-standard calenadar that becomes a little more involved.  If that is the case, check out this blog from the sql bi guys:

https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/

 

After you get those two columns you can use the following two measures:

Sample WTD=
CALCULATE(
    [MEASURE],
    FILTER(
        ALL( DATE),
        MAX( DATE[YEAR]) = DATE[YEAR]
        &&
        MAX( DATE[WEEKNUM]) = DATE[WEEKNUM]
    )
)

Sample PWTD=
CALCULATE(
    [MEASURE],
    FILTER(
        ALL( DATE),
        MAX( DATE[YEAR]) - 1 = DATE[YEAR]
        &&
        MAX( DATE[WEEKNUM]) = DATE[WEEKNUM]
    )
)

give it try and see what you come up with. 🙂

tmears Member
Member

Re: WTD Verus last WTD

@Nick_M

unfortunaelty this didnt work, however i think i am getting somewhere:

Sample PWTD =
CALCULATE(
[Count of Sales History ID],
FILTER(
'Date',
YEAR ('Date'[Date]) = Year (NOW ())
&&
WEEKNUM( 'Date'[Date],2) = WEEKNUM(NOW(),2) -1) )
 
however this returns the full number of quotes last week., however I need to
limit this to the same number of days as this week ie
if today is thursday, i need all the sales from Monday to Thursday last week
If today was friday i need all the sales from Monday to Friday Last week etc etc
 
Community Support Team
Community Support Team

Re: WTD Verus last WTD

hi, @tmears 

You could try this formula as below:

Measure 2 = 
CALCULATE (
    [Count of Sales History ID],
    FILTER (
        ALL ( 'Date'[Date] ),
        
            YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) )
            && WEEKNUM('Date'[Date],2)= MAXX('Date', WEEKNUM('Date'[Date],2))-1
            && DATEDIFF('Date'[Date]-1,TODAY(),WEEK)=1
            && WEEKNUM ( 'Date'[Date], 2 ) = WEEKNUM ( NOW (), 2 )-1
            &&WEEKDAY('Date'[Date],2)<=MAXX('Date', WEEKDAY('Date'[Date],2))          
    )
)

Result:

2.JPG

 

and here is sample pbix file, please try it.

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: WTD Verus last WTD

@tmears 

Can you upload a sample of your data?  The measures I referenced work ( I use them in my day-to-day job) but there could be other issues with your date table or I mistyped something, but would need to see some data 

tmears Member
Member

Re: WTD Verus last WTD

sample file

@Nick_M 

I have enclosed a sample file, thanks for your help on this

Super User
Super User

Re: WTD Verus last WTD

@tmears ,

I attached the pbix below, but here's an overview of what I did. The date table you were using didnt have the all the dates for 2019. I looked at the code but couldnt see how to fix that. So I created a new date table using power query.  You can look at the applied steps and see what I did. I basically needed a way to have a cumulative week number, year, and the day week #.  I added in the Month Name ( and Month to sort the month name) as well. I then created a datekey to match your datekey in the sales table.  Once loaded I made sure to mark that as date table, and related that to your fact table. 

 

Here's the M code, but probably easier to step through

 

let
    Source = Query1(2018, 2019),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Inserted Week of Year" = Table.AddColumn(#"Renamed Columns", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Inserted Year" = Table.AddColumn(#"Inserted Week of Year", "Year", each Date.Year([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Year", {"Year", "Week of Year"}, {{"Count", each _, type table [Date=date, Week of Year=number, Year=number]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Index",{"Index", "Count"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Date"}, {"Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Count",{"Date", "Index"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Index", "WeekNum"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "DateKey", each Text.Combine(
    {
        Text.From( Date.Year([Date]) ),

        Text.PadStart(
            Text.From(
                Date.Month([Date])
            ),
            2,
            "0"
        ),

        Text.PadStart(
            Text.From(
                Date.Day([Date])
            ),
            2,
            "0"
        )
    }
)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"DateKey", type text}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type1", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Month" = Table.AddColumn(#"Inserted Month Name", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Year1" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Year1", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type)
in
    #"Inserted Day of Week"

Now that we have a proper date table, I put Year, Week Number and date in a matrix. Then I started with your Count measure.  Next measure was the week to date:

Week to Date = 
--Set Variables
Var __CurrentWeekNum= MAX( NewDateTable[WeekNum]) 
Var __CurrentDate=MAX( NewDateTable[Date])
return

IF( 
    COUNTROWS(FactSalesHistory)>0,                           --Prevents figures from being projected into future dates
        CALCULATE(
            [COunt of Sales History ID1],
                Filter(
                 ALL( NewDateTable),                        --Remove all the filters from the NewDate Table
                  __CurrentWeekNum = NewDateTable[WeekNum]  --Then only want rows where the weeknumnber = the current weeknumber in the current filter context
                  &&
                  __CurrentDate >= NewDateTable[Date]       --And where the current date is >= the date. So that we get a list of dates for the running total
                )
            )
        )
Prev Week To Date = 
--Set Variables for easier code
VAR __CurrentWeek = Max( NewDateTable[WeekNum])
Var __PrevWeek = __CurrentWeek - 1
Var __CurrentDay=max( NewDateTable[Day of Week])
RETURN

IF( 
    COUNTROWS(FactSalesHistory) >0,
        CALCULATE(
            [COunt of Sales History ID1],
        FILTER(
            ALL( NewDateTable),
            NewDateTable[WeekNum] = __PrevWeek          --Now want all the rows in the date table that equal the previous weeknumber
            &&
            __CurrentDay >=NewDateTable[Day of Week]    --Then only want those day numbers that are equal or > the current day number     
            
        )
    )
)

Final Matrix with Code.png

 

 

View solution in original post

tmears Member
Member

Re: WTD Verus last WTD

@Nick_M  you are an absolute legend.  Thank you so much.

tmears Member
Member

Re: WTD Verus last WTD

@Nick_M  it worked a dream, but as always they now what MTD and Previous MTD.  so just working through this just thought i would be cheeky and see if you agree:

 

Month to Date Measure =
--Set Variables
Var __CurrentMonthNum= MAX( 'Calendar'[Month Num])
Var __CurrentDate=MAX( 'Calendar'[Date])
return

IF(
COUNTROWS('FactSalesHistory')>0, --Prevents figures from being projected into future dates
CALCULATE([Count of SalesHistory],
Filter(
ALL( 'Calendar'), --Remove all the filters from the NewDate (Calendar) Table
__CurrentMonthNum = 'Calendar'[Month Num] --Then only want rows where the weeknumnber = the current weeknumber in the current filter context
&&
__CurrentDate >= 'Calendar'[Date] --And where the current date is >= the date. So that we get a list of dates for the running total
)
)
)
 
and
Prev Month To Date Measure =
--Set Variables for easier code
VAR __CurrentMonth = Max( 'Calendar'[Month Num])
Var __Prevmonth = __Currentmonth - 1
Var __CurrentDay=max( 'Calendar'[Weekday Num])
RETURN

IF(
COUNTROWS('FactSalesHistory') >0,
CALCULATE(
[Count of SalesHistory],
FILTER(
ALL( 'Calendar'),
'Calendar'[Month Num] = __Prevmonth --Now want all the rows in the date table that equal the previous weeknumber
&&
__CurrentDay >='Calendar'[Weekday Num] --Then only want those day numbers that are equal or > the current day number
 
)
)
)
 
 
Super User
Super User

Re: WTD Verus last WTD

@tmears 

That may work, just have to try it out and see.  But I'd rather just use the built in time intelligence functions.

 

You can use this for Total MTD

https://docs.microsoft.com/en-us/dax/totalmtd-function-dax

 

Then you can use the previous month function:

https://docs.microsoft.com/en-us/dax/previousmonth-function-dax

 

The cool thing is that you can nest the different time intelligence functions. Generic code would look like this:

Total MTD=
TOTALMTD( [MEASURE], DateTable[DateKey])

Prev MTD=
CALCULATE(
    [Total MTD],
    PREVIOUSMONTH(DateTable[DateKey])
)

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 405 members 3,650 guests
Please welcome our newest community members: