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.
Hi guys,
I have this table below, the only in my model. As you can see, the Volume (CY_B_Vol) only shows in the first week of each month.
I would like then to be split in weeks according to how many weeks we have in a month (if the month has 5 weeks, split (CY_B_Vol) by 20 if the month has 4 weeks split (CY_B_Vol) by 25), like in the yellow column.
It seems like a simple thing to do, but I've been struggling with this for 2 days without a solution. ☹️
Could anybody help me, please?
Thanks!
Solved! Go to Solution.
Hi , Try below
Firstly create a calculated column with below dax for filling all the rows Column 2 = var cur='Table'[monthofyear] return if('Table'[monthofyear]=cur,(calculate(sum('Table'[cy-b-col]),filter('Table','Table'[monthofyear]=cur))))
Second Create an calculated TABLE with below dax and create a relationship between main table and this table with [monthofyear] Output = SUMMARIZE ( 'Table','Table'[monthofyear], "Count value", COUNT ( 'Table'[monthofyear] ) )
Now final create a calculated column as below CalculatedColumn = DIVIDE('Table'[Column 2],if(RELATED(Output[Count value])=5,20,25))
..Its a bit lengthy but will serve your purpose...
Hi , Try below
Firstly create a calculated column with below dax for filling all the rows Column 2 = var cur='Table'[monthofyear] return if('Table'[monthofyear]=cur,(calculate(sum('Table'[cy-b-col]),filter('Table','Table'[monthofyear]=cur))))
Second Create an calculated TABLE with below dax and create a relationship between main table and this table with [monthofyear] Output = SUMMARIZE ( 'Table','Table'[monthofyear], "Count value", COUNT ( 'Table'[monthofyear] ) )
Now final create a calculated column as below CalculatedColumn = DIVIDE('Table'[Column 2],if(RELATED(Output[Count value])=5,20,25))
..Its a bit lengthy but will serve your purpose...
In Power Query Editor screen, select column CY_B_VOL and click on Fill (Down) in the Transform tab:
Then click Close & Apply and add a calculated column to your table:
CalculatedColumn =
VAR _curMonth = Table[Month_of_Year]
RETURN
DIVIDE(Table[CY_B_Vol], COUNTROWS(FILTER(ALL(Table), Table[Month_of_Year] = _curMonth)))
Let me know if this works 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Hi @JarroVGIT, thanks for your quick reply.
It actually didn't work. =/
First, the volume should not fill down, as I want the count to be done with the first value only.
Second, I need to divide the volume that appears in the first week of each month.
If the month is equal to 3,6,9 or 12, I would divide by 20, the others I would divide by 25.
Anyhow, I appreciate your help 😃
Does your current model look like the table you gave us but without the yellow column? Or did I understand your question wrong? I assumed you have the white columns and you wanted the yellow column. Or are you missing the weeknumbers as well?
Proud to be a Super User!
Hi @JarroVGIT , tks again 😃
Does your current model look like the table you gave us but without the yellow column? Correct! The yellow column is exactly what I want.
Or did I understand your question wrong? I assumed you have the white columns and you wanted the yellow column. Correct again
Or are you missing the weeknumbers as well? No, I'm not.
As you can notice, I'm a newbie. I don't know if my logic is good, but I thought maybe get the minimum value for the week (where the month is equal the selected one and divide (with if based on the month as well); however, I'm still trying to accomplish this.
I really appreciate your patience and attention 😃
Proud to be a Super User!
Proud to be a Super User!
Proud to be a Super User!
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
35 | |
32 | |
18 | |
18 |