Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to Solution.
@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 ) ) )
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:
and here is sample pbix file, please try it.
Best Regards,
Lin
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:
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 ,
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 ) ) )
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:
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]) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |