topic Re: Calculate CY sales based on weeks in DAX Commands and Tips

Calculate CY sales based on weeks

ns292022 — Fri, 25 Nov 2022 03:56:55 GMT

Hello Friends,

I have the following table, where datediff is the difference in weeks from current date

The output should be:

I created a new column Name using the formula:

Name = if(Enrolment_new[datediff]=1,"Last Week",if(Enrolment_new[datediff]<=4 && Enrolment_new[datediff]>0 ,"Last 4 Weeks",if(Enrolment_new[datediff]<=12 && Enrolment_new[datediff]>0,"Last 12 Weeks",if(Enrolment_new[datediff]<=52 && Enrolment_new[datediff]>0,"Last 52 Weeks"," "))))

However the summarize table is excluding Last Week data from Last week 4 weeks data, Last 4 weeks data from Last 12 weeks data and so on. Can someone help me on how to get data in correct form where CY, PY values of Last 4 weeks should include Last week and so on.

Thanks!

Re: Calculate CY sales based on weeks

FreemanZ — Fri, 25 Nov 2022 04:58:21 GMT

Grouping with IF needs the the groups to be mutually exclusive. In your case, i would suggest to do it with measures. Try to create measures with the code below：

CYLW :=

CALCULATE (

SUM(TableName[CY]),

TableName[datediff] <=1

)

CY4W :=

CALCULATE (

SUM(TableName[CY]),

TableName[datediff] <=4

)

CY12W :=

CALCULATE (

SUM(TableName[CY]),

TableName[datediff] <=12

)

CY52W :=

CALCULATE (

SUM(TableName[CY]),

TableName[datediff] <=52

)

then try to plot the Table Visual with these measures.

Re: Calculate CY sales based on weeks

NikhilChenna — Fri, 25 Nov 2022 04:57:07 GMT

Hi ns292022 , I think below is the solution for you.

1. In the table you are not showing the datediff where it is 1, so i used the sample data provided by you and also and one entry for datediff 1 as below.

2. Please use this below calculated dax column for Name column -

Name =

IF('Table (2)'[datediff]=1,"Last Week",

IF('Table (2)'[datediff]>1 && 'Table (2)'[datediff]<=4,"Last 4 Weeks",

IF('Table (2)'[datediff]>4 && 'Table (2)'[datediff]<=12,"Last 12 Weeks",

IF('Table (2)'[datediff]>12 && 'Table (2)'[datediff]<=52,"Last 52 Weeks",BLANK()

))))

3. in this just replace the table name and nothing else. this is the output I'm getting,

This will work for you.

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

Re: Calculate CY sales based on weeks

ns292022 — Fri, 25 Nov 2022 05:48:48 GMT

Thanks for sharing this, however the output that I need should give the summation of last week and last 4 weeks values as CY, i;e Last week = 164, Last 4 Weeks = 164+304=468, as last 4 weeks also includes last week but with my approach as I have already tagged datediff 1 as Last Week it is excluding 1 from last 4 weeks. Here is the sample data

Re: Calculate CY sales based on weeks

ns292022 — Fri, 25 Nov 2022 06:31:33 GMT

Thanks for sharing this but in this way I have to create 4 diffrent measures for CY whereas I need the values in 1 column like this:

here is the sample data

I cannot change the output . Thanks!

Re: Calculate CY sales based on weeks

NikhilChenna — Fri, 25 Nov 2022 05:55:42 GMT

Hi ns292022 ,

Correct , as you have tagged it has 1 it is showing separtely as last week. If you remove the last week condition and just keep >=1 and <=4,"Last 4 Weeks", it will work.

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

Re: Calculate CY sales based on weeks

ns292022 — Fri, 25 Nov 2022 07:54:19 GMT

yes, but I have to show data separately for Last Week, Last 4 Weeks, Last 12 Weeks and Last 52 Weeks and thats where I am stuck badly.

Re: Calculate CY sales based on weeks

Anonymous — Tue, 06 Dec 2022 06:14:12 GMT

Hi ns292022 ,

Please share some sample data in text format so that we could test formulas.