Forum Discussion
Visualize Current and Previous Week in cards
This might have been solved, I have tried some solutions however I haven't achieved what I need.
So the situation is this, I have a table like this:
| id | article | evaluation_results | date |
| 1 | a | 7.3 | 5-ago-24 |
| 2 | b | 8.1 | 4-ago-24 |
| 3 | c | 0.3 | 5-ago-24 |
| 4 | d | 4.5 | 6-ago-24 |
| 5 | a | 1.2 | 29-jul-24 |
| 6 | b | 0.9 | 30-jul-24 |
| 7 | c | 2.3 | 29-jul-24 |
| 8 | d | 6.7 | 2-ago-24 |
So, this is just a sample of what I have.
What I need to have is a card that shows the values from the latest week, and a second card to show the results from the week before. I can visualize the latest one, but haven't achieved to do it for the previous one.
So, I have the card to visualize the results for a, b, c, d, or combined (by using filters for article)
But haven't achieved to see them for the week between 29-jul and august 3.
I have already tried by creating a calendar table, and create the relationship with the date in my results table. I think my problem is in my DAX formula
Previous week = calculate([Total], datesbetween( calendar[date], min(calendar[date]) - 7, min(calendar[date)))
And then added that measure to the card visual
I have tried this:
Last Week =
VAR CurrentWeek = WEEKNUM(TODAY(), 21)
VAR CurrentYear = YEAR(TODAY())
VAR PreviousWeek = IF(CurrentWeek = 1, CALCULATE(MAX('Table'[Week]), ALL('Table'), 'Table'[Year] = CurrentYear - 1), CurrentWeek - 1)
VAR PreviousYear = IF(CurrentWeek = 1, CurrentYear - 1, CurrentYear)
RETURN
SUMX(
FILTER(
ALL('Table'),
'Table'[Week] = PreviousWeek && 'Table'[Year] = PreviousYear
),
[Total Evaluation]
)
It's been able to calculate the whole SUM for all the articles in the previous week, between 29-jul and august 3 in this case
However, when I add a Slicer (Tile) to select the article, I should get the SUM just for that article, but that is not happening and still gives me the SUM for the whole week. In resume, the slicer is not working properly.
Thanks in advance
- Anonymous2 years ago
Hi Padagon ,
Based on my testing, please try the following methods:
1.Create the sample table.
2.Create the measure to calculate the previous week.
Previous week = CALCULATE( SUM('Table'[evaluation_results]), FILTER( 'Table', 'Table'[date] >= MIN('Table'[date]) && 'Table'[date] <= MIN('Table'[date]) + 6 ) )3.Create the measure to calculate the current week.
Current week = CALCULATE( SUM('Table'[evaluation_results]), FILTER( 'Table', 'Table'[date] > MIN('Table'[date]) + 6 && 'Table'[date] <= TODAY() ) )4.Drag the measures into the card visual.
5.Drag the article into the card visual. The result is shown below.
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- audreygerredSuper User
Hi! Here is a link that should help with WoW: Week-related calculations – DAX Patterns
- AnonymousNot applicable
Hi Padagon ,
Based on my testing, please try the following methods:
1.Create the sample table.
2.Create the measure to calculate the previous week.
Previous week = CALCULATE( SUM('Table'[evaluation_results]), FILTER( 'Table', 'Table'[date] >= MIN('Table'[date]) && 'Table'[date] <= MIN('Table'[date]) + 6 ) )3.Create the measure to calculate the current week.
Current week = CALCULATE( SUM('Table'[evaluation_results]), FILTER( 'Table', 'Table'[date] > MIN('Table'[date]) + 6 && 'Table'[date] <= TODAY() ) )4.Drag the measures into the card visual.
5.Drag the article into the card visual. The result is shown below.
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.