Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have data table with sales products by day for the currrent quarter [tblSales]
InvoiceDate | Product | Qty Sold
I have a table that has date period selections [tblDateSelections]
Date Period | Start Date | End Date
Within this table i have entries for Yesterday, WTD, Last Complete Week etc all with the relevant start date,end date and integer identifying the selection made
EG: Last Complete Week | 19-02-2018 | 26-02-2018 | 1
Within the report i have a slicer based on date selection table, i can see this working and the value change when i make a selection.
msrDatePeriod = IF(HASONEVALUE(DatePeriod[TimePeriod]),VALUES(DatePeriod[Value]),5)
What I want to do is create a column in tblSales that flags the relevant records based on the date selection, ideally flagging with a 1. Which i would then use as a filter criteria for visualisations. This way i can show QTD and any other date period selected.
Any help would be greatly appreciated.
Cheers
Do you have a limited number of values in the "Date Period" column?
If so then you can create a new column on your [tblSales] table for each value.
E.g. This Week = if(lookupvalue(tblDateSelections[Start Date],tblDateSelections[Date Period],"This Week") <= [InvoiceDate] && lookupvalue(tblDateSelections[End Date],tblDateSelections[Date Period],"This Week") >= [InvoiceDate],1,0)
Assuming you had a date range called "This Week".
This solution would only give me a 1 or 0 for that date period. I have columns within the tblSales that flag each date range already eg WTD-Flag, MTD-Flag etc. In an ideal world what I want to do is;
Hope this makes sense and thanks for the proposed answer.
Cheers
@SmithyBowMassiv I know many years have passed, were u able to solve this problem? any idea how?
Let me see if I can restate the problem, to see if I understand.
You want a column on the Sales data that shows whether this record has been selected based on the filtered values in the Dates table?
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |