Table issue

[Jyo27]

Hi everyone,

Need help to create measures . Can anyone please help to resolve this issue . 

input :

Category	qty	week
A	1	12
B	2	12
C	2	12
D	1	12
E	3	11
F	3	11

output:

current week	Lastweek
A	E
B	F
C
D
count:4	count:2

[Anonymous · Accepted solution]

Hi @Jyo27 

You can refer to the following example

Create the following measures

Rank = COUNTROWS(FILTER(ALLSELECTED('Table'),[week] in VALUES('Table'[week])&&[Category ]<=SELECTEDVALUE('Table'[Category ])))

LastWeek =
VAR a =
    CALCULATE ( [Rank] )
VAR b =
    MAXX ( ALLSELECTED ( 'Table' ), [week] )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[week] ) = b,
        IF (
            CALCULATE (
                MAX ( 'Table'[Category ] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    [week]
                        = SELECTEDVALUE ( 'Table'[week] ) - 1
                        && [Rank] = a
                )
            )
                <> BLANK (),
            CALCULATE (
                MAX ( 'Table'[Category ] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    [week]
                        = SELECTEDVALUE ( 'Table'[week] ) - 1
                        && [Rank] = a
                )
            ),
            "null"
        )
    )

Currentweek_counts = COUNTROWS(FILTER(ALLSELECTED('Table'),[week]=MAXX(ALLSELECTED('Table'),[week]))) 

lastweek_counts = COUNTROWS(FILTER(ALLSELECTED('Table'),[LastWeek]<>"null"&&[week]=MAXX(ALLSELECTED('Table'),[week])))

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jyo27]

when i select week  ,it should show current week and previous week categories .