Forum Discussion

Padagon's avatar
Padagon
Frequent Visitor
2 years ago
Solved

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:

 

idarticleevaluation_resultsdate
1a7.35-ago-24
2b8.14-ago-24
3c0.35-ago-24
4d4.56-ago-24
5a1.229-jul-24
6b0.930-jul-24
7c2.329-jul-24
8d6.72-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

  • Anonymous's avatar
    Anonymous
    2 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

  • Anonymous's avatar
    Anonymous
    Not 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.