Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Hi @ns292022 ,
Please share some sample data in text format so that we could test formulas.
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 -
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
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!
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.
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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |