Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tmears
Helper III
Helper III

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
Anonymous
Not applicable

@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

10 REPLIES 10
v-lili6-msft
Community Support
Community Support

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.
Anonymous
Not applicable

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. 🙂

@Anonymous

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
 
Anonymous
Not applicable

@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 

sample file

@Anonymous 

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

Anonymous
Not applicable

@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

 

 

Hi @Anonymous ,

 

I have similar kind of WTD calculation but I am struggling to get dynamic WTD values based on slicer selection.

For Example:

As of Feb 04, 2020 (Today())

WeekNum = 6

WeekDayNum = 01 (00 being Monday - 06Sunday).

 

I calculated similar WeekNum and Weekday on Ordered Date from fact table.

 

TimeDimension table is joined to FactTable (Datecolumn - Ordered date)

 

I got WTD measure working but when I throw on to a detail level of table/visual with Fact table date field, it is getting all the rows in the fact table but total value on measures is showing WTD.

How do I filter only WTD rows based on ordered date (fact table) slicer selection.

 

Thanks a lot.

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

@Anonymous  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
 
)
)
)
 
 
Anonymous
Not applicable

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.