Forum Discussion
Tagging date column with 'previous' and 'current' tag
Hi All,
I want to tag my date table with another column that says if it is current or previous. 'Previous' should be tagged against all dates that are a week prior to MAX date, however, the tag should be continuous until MIN date.
I.e.
Date | Current | Previous |
1/1/21 | Current | Previous |
3/1/21 | Current | Previous |
6/1/21 | Current | Previous |
7/1/21 | Current | Previous |
9/1/21 | Current |
|
Reason I want to do this is because I want to use this as a filter on a matrix for previous week cumulative sales. When I use a measure, for some reason, the values in the matrix aren't adding up as expected so I'm wanting a column so that I can simply put a filter.
Hi Anonymous ,
It would not be a good solution for your issue. You'd better create new weeknum column: weeknum = WEEKNUM(Date[Date]), then you can use the following measure to calculate previous week cumulative sales.
Measure = CALCULATE(SUM(Table[sales]),Filter(Date,Date[weeknum]<= WEEKNUM(Today())-1 ))If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
4 Replies
- mahoneypat
Microsoft Employee
Not exactly what you are asking for, but this video walks through how to make columns like this on your Date table (or other table) to give you week/month/quarter indices you can use in visuals and filters like you describe.
Power BI Tales From The Front - Day/Week/Month/Quarter/Year Indices - YouTube
Pat - PhilipTreacy
Super User
Hi Anonymous
This will create your column.
CurrPrev = IF([Date] >= MAX([Date])-7, "Previous", "Current")Regards
Phil
- Ashish_Mathur
Hi,
We should ideally not need that column. The Date Intelligence functions should be able to get us what we want.
v-deddai1-msftCommunity Support
Hi Anonymous ,
It would not be a good solution for your issue. You'd better create new weeknum column: weeknum = WEEKNUM(Date[Date]), then you can use the following measure to calculate previous week cumulative sales.
Measure = CALCULATE(SUM(Table[sales]),Filter(Date,Date[weeknum]<= WEEKNUM(Today())-1 ))If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
