Creating week on week view in Matrix similar to Pivot table in excel

[Saibal_78]

I have a data set of tickets logged on weekly basis, I need to create a view that would give me  current week Vs. previous week along with Delta column. Data has columns like Ticket category, Priority, Week & Year, Week No, Year and Ticket Count. Below is the sample of the data.

I have tried creating previous week ticket count by using below DAX formula, but the not able to put it in matrix to get a pivot view with delta column

Previous Week Incidents =
Var CurrentWeek = SELECTEDVALUE(DATA[Week No])
Var CurrentYear = SELECTEDVALUE(DATA[Year])
Var MaxWeekNumber = Calculate(Max(DATA[Week No]), ALL(DATA))

Return
SUMX(
Filter(ALL(DATA),
    if(CurrentWeek = 1,
        DATA[Week No] = MaxWeekNumber && DATA[Year] = CurrentYear - 1,
        DATA[Week No] = CurrentWeek - 1 && DATA[Year] = CurrentYear)),
        [Ticket Count])

The final view that I want is like this

Any help would be highly appreciated, Thanks

[v-diye-msft]

Hi @Saibal_78 ,

Could you please share us your dummy pbix/excel ? it's preferred to upload your file (Remove the confidential info)  in OneDrive and share us the link. I understood your point, but can barely replicate your data.

Best regards,

Dina Ye

[Saibal_78]

Hi Dina

Thanks for looking into this, below is the OneDrive link where I uploaded my workflow

https://onedrive.live.com/?id=3E7AAA9BEE73122F%21104&cid=3E7AAA9BEE73122F

Thanks

[v-diye-msft]

Hi @Saibal_78 ,

The pbix you attached is different with the data you indicated previously.  To generate the difference% as you requested, I try to add the measure based on the data in your pbix:

Attached here for your reference; https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/ET7ZCyKVVC9PvsIBYxgXOo...

[Saibal_78]

Hi Dina,

Let me send you the excel file to you. The solution you sent actually is not something I was looking at

https://onedrive.live.com/?id=3E7AAA9BEE73122F%21104&cid=3E7AAA9BEE73122F

[v-diye-msft]

Hi @Saibal_78 ,

The file in your link is not accessible. I'm not able to download the excel. could you please resend the file via dropbox/OneDrive for business?

[Saibal_78]

Hi Dina,

Can you check one again

https://onedrive.live.com/?id=3E7AAA9BEE73122F%21104&cid=3E7AAA9BEE73122F

[v-diye-msft]

Hi @Saibal_78 

Still unaccessible: it seems the link shared is not public and need specific permission : 

[Saibal_78 · Accepted solution]

Finally solved with below measure

Measure 1

Current Week Count =

VAR CurrWeek =

MAX(DATA[Week No])

RETURN

CALCULATE(

SUM(DATA[Filter]),

FILTER(ALLSELECTED(DATA),[Week No]=CurrWeek),

VALUES(DATA[Tower]),

VALUES(DATA[priority])

)

Measure 2

Previous Week Count =

VAR CurrWeek =

MAX(DATA[Week No])

RETURN

CALCULATE(

SUM(DATA[Filter]),

FILTER(ALLSELECTED(DATA),[Week No]=CurrWeek-1),

VALUES(DATA[Tower]),

VALUES(DATA[priority])

)

Measure 3

Delta = VAR CurrWeek =

MAX(DATA[Week No])

RETURN

CALCULATE(

SUM(DATA[Filter]),

FILTER(ALLSELECTED(DATA),[Week No]=CurrWeek),

VALUES(DATA[Tower]),

VALUES(DATA[priority])

)

- CALCULATE(

SUM(DATA[Filter]),

FILTER(ALLSELECTED(DATA),[Week No]=CurrWeek-1),

VALUES(DATA[Tower]),

VALUES(DATA[priority])

)

Measure 4

Delta % =

VAR CurrWeek =

MAX(DATA[Week No])

RETURN

CALCULATE(

DIVIDE([Delta],[Previous Week Count],0),

VALUES(DATA[Tower]),

VALUES(DATA[priority])

)

[v-diye-msft]

Hi @Saibal_78 ,

Sorry for my late reply. Great to know you've nailed it!