Filtering the number of days between today and a specific date
Hi, I need to obtain the following number as a measure for me to do some additional calculations: number of days between today and a specific date within the data set. I have added a calculated column in my date calendar to get the time difference in days between today and all the available days. Then I made another column with a marker for the date I have to take into account (not the first or last date of the dataset, so I can't use MIN or MAX as a get around). I can make a card with this as long as I use an additional filter to the visual (filter set on the 2nd column I mentioned). To get the same number as a measure, I thought I could use the following filter: The error message I get doesn't make sense as of now. How can I get around this? Thanks, Pauline.
Rolling 12 WEEK Measure
Hi all, The funny thing about DAX is that as soon as I feel like I'm getting the hang of things...a new problem pops up which brings me back to earth. In my current dilemma, I cannot seem to come up with a way to compute a rolling 12 week total for shipped COGS (cost of goods sold). Here's a bit of background: In my model there is a fact table named Fact_B2BSalesDiagnostic comprised of several Excel files appended together using PowerQuery. Each Excel file is a table which reports sales figures for multiple products across a single week in time...where each row represents a specific product's performance in the given week. You can imagine a row in one of the original Excel files as resembling something like: (ProductNumber, ProductName, UnitsShipped, ShippedCOGS) While connecting PowerQuery to the folder containing the Excel files, the Week Start and Week End columns were appended to each row. In this way, we can define a relationship between the Date field of Dim_Calendar and either Week Start or Week End of Fact_B2BSalesDiagnostic in order to define measures which can achieve weekly totals. The image below depicts the relationship I have used: Additionally, I have defined YrWkRngKey and YrWkRngSort fields in Dim_Calendar. The best way to describe these fields is through example: Assume we are looking at a row in Dim_Calendar with a Date of 28-Sep-2021 The week range (sunday to sunday) this date belongs to is 26-Sep-2021 thru 2-Oct-2021 As such, the YrWkRngKey reads: "2021|9/26-10/2" (Note each row with date between 26Sep21 and 2Oct21 will have this same exact value) The YrWkRngSort field is simply an index column which numbers the unique values of YrWkRngKey Having defined things as such, I was able to create a measure which computes ShippedCOGS for the prior period as follows: [B2BShippedCOGS - PriorPeriod] := CALCULATE ( [B2BShippedCOGS], FILTER ( ALL ( Dim_Calendar ), CONTAINS ( VALUES ( Dim_Calendar[YrWkRngSort] ), Dim_Calendar[YrWkRngSort], Dim_Calendar[YrWkRngSort] + 1 ) ) ) Where: [B2BShippedCOGS] := SUM( Fact_B2BSalesDiagnostic[Shipped COGS] ) However -- I can't seem to find a way to get a rolling 12 week total for shipped COGS! I've been reading articles and trying things for 2 hours now without any luck, and I'm turning to you folks in the hopes that your expertise can help me get through this! Thanks in advance for any help you may offer. Hope you have a good one!Attempt 2: Rolling 12 week measure
Hi all, I posted here 2 days ago – and despite numerous views on my original post – I have yet to get any feedback. I feel that this is my own fault as my original post may have been too verbose without providing any data for anyone to work with. I am now reattempting with an effort to keep things concise. I will also provide data for everyone this time in hopes it will help others to help me. If you are interested, my original post can be read here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Rolling-12-WEEK-Measure/m-p/2171184#M50417 Goal: I would like to compute a measure which can achieve a rolling 12-week ShippedCOGS total. If you are unfamiliar with COGS – for the sake of this post you can consider it as being equivalent to Sales(USD). Below is an example of what the output should produce: Model: The problem distills down to just two tables: Dim_Calendar and Fact_B2BSalesDiagnostic. There is a 1:many relationship between the two tables with the 1-side tied to the Date field in Dim_Calendar and the many-side tied to the Week Start field in Fact_B2BSalesDiagnostic: Data: Since I am a new member, I do not yet have privilages to attach a PBIX file to this post. As an alternative, here is the M code which produces the Dim_Calendar table being used: *** EDIT: I've figured out I can use Google Drive to share the file here. I'll link the url below, but will also keep the dummy data in the post in the event you are not comfortable downloading from strange links on the internet (trust me: I get it): https://drive.google.com/file/d/1zbK_DO4HlIBDm9Xq64BqncDaq5HLj0kA/view?usp=sharing *** let Source = #date(2021, 1, 1), Custom1 = List.Dates(Source, Number.From(DateTime.LocalNow()) - Number.From(Source), #duration(1, 0, 0, 0)), #"Converted to Table" = Table.FromList(Custom1, 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 Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type), #"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type), #"Added Custom" = Table.AddColumn(#"Inserted Quarter", "QuarterAbbr", each "Q" & Number.ToText([Quarter])), #"Inserted Month" = Table.AddColumn(#"Added Custom", "Month", each Date.Month([Date]), Int64.Type), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Month",{{"Month", "MonthNumber"}}), #"Inserted Month Name" = Table.AddColumn(#"Renamed Columns1", "Month Name", each Date.MonthName([Date]), type text), #"Renamed Columns2" = Table.RenameColumns(#"Inserted Month Name",{{"Month Name", "Month"}}), #"Added Custom1" = Table.AddColumn(#"Renamed Columns2", "MonthAbbr", each Text.Start([Month], 3)), #"Inserted Week of Year" = Table.AddColumn(#"Added Custom1", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type), #"Renamed Columns3" = Table.RenameColumns(#"Inserted Week of Year",{{"Week of Year", "WeekNumber"}}), #"Inserted Start of Week" = Table.AddColumn(#"Renamed Columns3", "Start of Week", each Date.StartOfWeek([Date]), type date), #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date]), type date), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Inserted End of Week", {{"Start of Week", each Text.BeforeDelimiter(Text.From(_, "en-US"), "/", 1), type text}}), #"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Extracted Text Before Delimiter", {{"End of Week", each Text.BeforeDelimiter(Text.From(_, "en-US"), "/", 1), type text}}), #"Merged Columns" = Table.CombineColumns(#"Extracted Text Before Delimiter1",{"Start of Week", "End of Week"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Week Range"), #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"QuarterAbbr", type text}, {"MonthAbbr", type text}}), #"Added Custom2" = Table.AddColumn(#"Changed Type1", "YrWkRngKey", each Number.ToText([Year]) & "|" & [Week Range]), #"Grouped Rows" = Table.Group(#"Added Custom2", {"YrWkRngKey"}, {{"All Rows", each _, type table [Date=nullable date, Year=number, Quarter=number, QuarterAbbr=nullable text, MonthNumber=number, Month=text, MonthAbbr=nullable text, WeekNumber=number, Week Range=text, YrWkRngKey=text]}}), #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "YrWkRngSort", 1, 1, Int64.Type), #"Expanded All Rows" = Table.ExpandTableColumn(#"Added Index", "All Rows", {"Date", "Year", "Quarter", "QuarterAbbr", "MonthNumber", "Month", "MonthAbbr", "WeekNumber", "Week Range", "YrWkRngKey"}, {"Date", "Year", "Quarter", "QuarterAbbr", "MonthNumber", "Month", "MonthAbbr", "WeekNumber", "Week Range", "YrWkRngKey.1"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded All Rows",{"Date", "Year", "Quarter", "QuarterAbbr", "MonthNumber", "Month", "MonthAbbr", "WeekNumber", "YrWkRngKey", "YrWkRngSort", "Week Range", "YrWkRngKey.1"}), #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"YrWkRngKey.1"}), #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"WeekNumber", Int64.Type}, {"MonthNumber", Int64.Type}, {"Quarter", Int64.Type}, {"Year", Int64.Type}}) in #"Changed Type2" Additionally, here is some dummy data for Fact_B2BSalesDiagnostic which was used to create the above PivotTable (+5 points if you get the movie references 😄 ) Week Start,Week End,ProductNumber,Product Title,Shipped COGS 6/13/2021,6/19/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$28.91 6/13/2021,6/19/2021,1001,Bright Red Swingline Staple,$70.18 6/13/2021,6/19/2021,1002,Turbo-Man Action Figure,$54.50 6/20/2021,6/26/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$29.68 6/20/2021,6/26/2021,1001,Bright Red Swingline Staple,$17.05 6/20/2021,6/26/2021,1002,Turbo-Man Action Figure,$50.91 6/27/2021,7/3/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$55.37 6/27/2021,7/3/2021,1001,Bright Red Swingline Staple,$72.71 6/27/2021,7/3/2021,1002,Turbo-Man Action Figure,$59.15 7/4/2021,7/10/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$62.45 7/4/2021,7/10/2021,1001,Bright Red Swingline Staple,$59.30 7/4/2021,7/10/2021,1002,Turbo-Man Action Figure,$61.59 7/11/2021,7/17/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$28.71 7/11/2021,7/17/2021,1001,Bright Red Swingline Staple,$63.30 7/11/2021,7/17/2021,1002,Turbo-Man Action Figure,$66.53 7/18/2021,7/24/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$41.40 7/18/2021,7/24/2021,1001,Bright Red Swingline Staple,$11.92 7/18/2021,7/24/2021,1002,Turbo-Man Action Figure,$30.24 7/25/2021,7/31/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$54.48 7/25/2021,7/31/2021,1001,Bright Red Swingline Staple,$19.59 7/25/2021,7/31/2021,1002,Turbo-Man Action Figure,$17.03 8/1/2021,8/7/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$57.65 8/1/2021,8/7/2021,1001,Bright Red Swingline Staple,$55.78 8/1/2021,8/7/2021,1002,Turbo-Man Action Figure,$31.21 8/8/2021,8/14/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$70.92 8/8/2021,8/14/2021,1001,Bright Red Swingline Staple,$32.97 8/8/2021,8/14/2021,1002,Turbo-Man Action Figure,$33.68 8/15/2021,8/21/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$47.05 8/15/2021,8/21/2021,1001,Bright Red Swingline Staple,$50.73 8/15/2021,8/21/2021,1002,Turbo-Man Action Figure,$62.98 8/22/2021,8/28/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$11.72 8/22/2021,8/28/2021,1001,Bright Red Swingline Staple,$10.43 8/22/2021,8/28/2021,1002,Turbo-Man Action Figure,$26.21 8/29/2021,9/4/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$70.95 8/29/2021,9/4/2021,1001,Bright Red Swingline Staple,$45.20 8/29/2021,9/4/2021,1002,Turbo-Man Action Figure,$21.10 9/5/2021,9/11/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$77.49 9/5/2021,9/11/2021,1001,Bright Red Swingline Staple,$49.57 9/5/2021,9/11/2021,1002,Turbo-Man Action Figure,$56.97 9/12/2021,9/18/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$64.70 9/12/2021,9/18/2021,1001,Bright Red Swingline Staple,$64.59 9/12/2021,9/18/2021,1002,Turbo-Man Action Figure,$72.10 9/19/2021,9/25/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$73.70 9/19/2021,9/25/2021,1001,Bright Red Swingline Staple,$27.55 9/19/2021,9/25/2021,1002,Turbo-Man Action Figure,$37.89 9/26/2021,10/2/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$78.75 9/26/2021,10/2/2021,1001,Bright Red Swingline Staple,$44.15 9/26/2021,10/2/2021,1002,Turbo-Man Action Figure,$12.24 10/3/2021,10/9/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$18.73 10/3/2021,10/9/2021,1001,Bright Red Swingline Staple,$62.62 10/3/2021,10/9/2021,1002,Turbo-Man Action Figure,$30.32 10/10/2021,10/16/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$70.31 10/10/2021,10/16/2021,1001,Bright Red Swingline Staple,$43.95 10/10/2021,10/16/2021,1002,Turbo-Man Action Figure,$22.86 10/17/2021,10/23/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$25.19 10/17/2021,10/23/2021,1001,Bright Red Swingline Staple,$28.10 10/17/2021,10/23/2021,1002,Turbo-Man Action Figure,$67.63 10/24/2021,10/31/2021,1000,"Red Ryder, Carbine Action, 200 Shot Range Model Air Rifle",$77.03 10/24/2021,10/31/2021,1001,Bright Red Swingline Staple,$57.82 10/24/2021,10/31/2021,1002,Turbo-Man Action Figure,$35.04 What I've been able to do: For the report I am building, I needed to compute the week-over-week percent change. This obvisouly relies on knowing the prior week's shipped COGS total...which the following measure accomplishes this: [ShippedCOGS - PriorPeriod] := CALCULATE ( [B2BShippedCOGS], FILTER ( ALL ( Dim_Calendar ), CONTAINS ( VALUES ( Dim_Calendar[YrWkRngSort] ), Dim_Calendar[YrWkRngSort], Dim_Calendar[YrWkRngSort] + 1 ) ) ) As always, thanks everyone in advance for any help you may offer! I hope we can crack this!calculate data for weekdays in future date
in our business we have a feature that lets users book a timeslot and weekday to recieve their service (weekly or BiWeekly) , for example ,user X booked his service to be Friday 10:00 AM Weekly , user Y has booked his service to be every Tuesday 1:00 PM BiWeekly So user X will recieve his service Friday 10:00AM (Every Week), and user Y will recieve his serive Tuesday 1:00 PM (Every Two Weeks) the request is : create a matrix for every future date that shows how many users have booked a specific timeslot , ex: tuesady 07-06-2021 Wednesday 07-07-2021 Thursday 07-08-2021 Friday 07-09-2021 Saturday 07-10-2021 Sunday 07-11-2021 Monday 07-12-2021 tuesady 07-13-2021 Wednesday 07-14-2021 Thursday 07-15-2021 Friday 07-16-2021 Saturday 07-17-2021 Sunday 07-18-2021 Monday 07-19-2021 tuesady 07-20-2021 Wednesday 07-21-2021 Thursday 07-22-2021 Friday 07-23-2021 10:00 AM 1 User 1 User 1 users 11:00 AM 12:00 PM 1:00 PM 1 User 1 User so as you can see , dates are in the futute , and do not exist in our database , the data i have is : -User ID -weekday -time -service date ( data will be shown once service date has passed , if the date hasnt come yet , data will be NULL) ex for the table : User ID Week day Time Serive date frequency 124 Tusday 6:00 PM 6/27/2021 weekly 567 Friday 11:00 AM NULL weekly 566 Sunday 4:00 PM NULL biweekly 433 Friday 3:00 PM NULL biweekly so how can i count total users who booked a service each weekday (weekly/biweekly) for dates in the futute?count data for weekdays in the future
in our business we have a feature that lets users book a timeslot and weekday to recieve their service (weekly or BiWeekly) , for example ,user X booked his service to be Friday 10:00 AM Weekly , user Y has booked his service to be every Tuesday 1:00 PM BiWeekly So user X will recieve his service Friday 10:00AM (Every Week), and user Y will recieve his serive Tuesday 1:00 PM (Every Two Weeks) the request is : create a matrix for every future date , how many users have booked a specific timeslot , ex: tuesady 07-06-2021 Wednesday 07-07-2021 Thursday 07-08-2021 Friday 07-09-2021 Saturday 07-10-2021 Sunday 07-11-2021 Monday 07-12-2021 tuesady 07-13-2021 Wednesday 07-14-2021 Thursday 07-15-2021 Friday 07-16-2021 Saturday 07-17-2021 Sunday 07-18-2021 Monday 07-19-2021 tuesady 07-20-2021 Wednesday 07-21-2021 Thursday 07-22-2021 Friday 07-23-2021 10:00 AM 1 User 1 User 1 users 11:00 AM 12:00 PM 1:00 PM 1 User 1 User so as you can see , dates are in the futute , and do not exist in our database , the data i have is : -User ID -weekday -time -service date ( data will be shown once service date has passed , if the date hasnt come yet , data will be NULL) ex for the table : User ID Week day Time Serive date frequency 124 Tusday 6:00 PM 6/27/2021 weekly 567 Friday 11:00 AM NULL weekly 566 Sunday 4:00 PM NULL biweekly 433 Friday 3:00 PM NULL biweekly so how can i count total users who booked a service each weekday (weekly/biweekly) for dates in the futute?
Filter month year column to exclude future dates from today
I have a dates key table that runs from Jan 2018 - Jan 2021. I have a month year column (Text Format): Month Year = FORMAT('DimDate'[DateS],"MMM YYYY") However it pulls in all dates in the dates key range: Is there a way to exclude future dates from: FORMAT('DimDate'[DateS],"MMM YYYY"),<=TODAY() Thanks in advancebased on selection cumulative total and previous year value show
Hi all, I want to show previous year value and cummulative total in table fomat i have source data year, month , i have concatenate year and month (Ex, September 2020), if i select september 2020, i need jan to sep2020 cummulative total value, and previous year 2019 full year value sample data Year Month Value Business 2019 jan 73,431.49 Allocated 2019 april 21,088.96 Consumer 2019 sep 11,203.00 Consumer 2019 dec 58.07 Expenses 2020 jan 13,234.34 Expenses 2020 april 75,690.32 Allocated 2020 sep 17,817.28 Consumer 2020 Oct 46.46 Expenses 2018 jan 71,822.32 Allocated 2018 april 18,293.38 Consumer 2018 sep 69,064.75 Expenses if i select Sep2020, i need cummulative total of jan2020 to sep2020 as a CY value , 2019 jan to Dec value as a PY value if i select Dec2019, i need cummulative total of jan to Dec2019 as a CY value then 2018 jan to dec as a PY value output how can I achieve this logic? v-yingjl Thanks Rajesh
