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!
I need help with this problem:
I have a percentage adjustment target at the level of the month, but the granularity is at the level of the day in the table.
I need to calculate the quarterly adjusted target.
More exactly: I have these percentages for 3 months:
DATE | PERCENT | QUARTER |
1/1/2022 | 0.5 | Q1 |
1/2/2022 | 0.5 | Q1 |
1/3/2022 | 0.5 | Q1 |
1/4/2022 | 0.5 | Q1 |
1/5/2022 | 0.5 | Q1 |
1/6/2022 | 0.5 | Q1 |
1/7/2022 | 0.5 | Q1 |
1/8/2022 | 0.5 | Q1 |
1/9/2022 | 0.5 | Q1 |
1/10/2022 | 0.5 | Q1 |
1/11/2022 | 0.5 | Q1 |
1/12/2022 | 0.5 | Q1 |
1/13/2022 | 0.5 | Q1 |
1/14/2022 | 0.5 | Q1 |
1/15/2022 | 0.5 | Q1 |
1/16/2022 | 0.5 | Q1 |
1/17/2022 | 0.5 | Q1 |
1/18/2022 | 0.5 | Q1 |
1/19/2022 | 0.5 | Q1 |
1/20/2022 | 0.5 | Q1 |
1/21/2022 | 0.5 | Q1 |
1/22/2022 | 0.5 | Q1 |
1/23/2022 | 0.5 | Q1 |
1/24/2022 | 0.5 | Q1 |
1/25/2022 | 0.5 | Q1 |
1/26/2022 | 0.5 | Q1 |
1/27/2022 | 0.5 | Q1 |
1/28/2022 | 0.5 | Q1 |
1/29/2022 | 0.5 | Q1 |
1/30/2022 | 0.5 | Q1 |
1/31/2022 | 0.5 | Q1 |
2/1/2022 | 1 | Q1 |
2/2/2022 | 1 | Q1 |
2/3/2022 | 1 | Q1 |
2/4/2022 | 1 | Q1 |
2/5/2022 | 1 | Q1 |
2/6/2022 | 1 | Q1 |
2/7/2022 | 1 | Q1 |
2/8/2022 | 1 | Q1 |
2/9/2022 | 1 | Q1 |
2/10/2022 | 1 | Q1 |
2/11/2022 | 1 | Q1 |
2/12/2022 | 1 | Q1 |
2/13/2022 | 1 | Q1 |
2/14/2022 | 1 | Q1 |
2/15/2022 | 1 | Q1 |
2/16/2022 | 1 | Q1 |
2/17/2022 | 1 | Q1 |
2/18/2022 | 1 | Q1 |
2/19/2022 | 1 | Q1 |
2/20/2022 | 1 | Q1 |
2/21/2022 | 1 | Q1 |
2/22/2022 | 1 | Q1 |
2/23/2022 | 1 | Q1 |
2/24/2022 | 1 | Q1 |
2/25/2022 | 1 | Q1 |
2/26/2022 | 1 | Q1 |
2/27/2022 | 1 | Q1 |
2/28/2022 | 1 | Q1 |
3/1/2022 | 1 | Q1 |
3/2/2022 | 1 | Q1 |
3/3/2022 | 1 | Q1 |
3/4/2022 | 1 | Q1 |
3/5/2022 | 1 | Q1 |
3/6/2022 | 1 | Q1 |
3/7/2022 | 1 | Q1 |
3/8/2022 | 1 | Q1 |
3/9/2022 | 1 | Q1 |
3/10/2022 | 1 | Q1 |
3/11/2022 | 1 | Q1 |
3/12/2022 | 1 | Q1 |
3/13/2022 | 1 | Q1 |
3/14/2022 | 1 | Q1 |
3/15/2022 | 1 | Q1 |
3/16/2022 | 1 | Q1 |
3/17/2022 | 1 | Q1 |
3/18/2022 | 1 | Q1 |
3/19/2022 | 1 | Q1 |
3/20/2022 | 1 | Q1 |
3/21/2022 | 1 | Q1 |
3/22/2022 | 1 | Q1 |
3/23/2022 | 1 | Q1 |
3/24/2022 | 1 | Q1 |
3/25/2022 | 1 | Q1 |
3/26/2022 | 1 | Q1 |
3/27/2022 | 1 | Q1 |
3/28/2022 | 1 | Q1 |
3/29/2022 | 1 | Q1 |
3/30/2022 | 1 | Q1 |
3/31/2022 | 1 | Q1 |
I should calculate the maximum percentage at the quarterly level:
And in the end I should multiply this percentage by the monthly TARGET:
Expected value = 2500
How i can do this measure in dax?
Thank you! ~~
Solved! Go to Solution.
Hi @ANM_97 ,
First you need to create a calculated column.
Month = FORMAT('Table'[DATE],"mmm")
Relationship.
Then create a new measure.
TARGET_AJUSTED =
SUMX (
SUMMARIZE (
'TARGET',
'TARGET'[MONTH],
'TARGET'[TARGET],
"max_of_percent", MAX ( 'Table'[PERCENT] )
),
'TARGET'[TARGET] * [max_of_percent]
)
Attached PBIX file for reference.
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Hi @ANM_97 ,
First you need to create a calculated column.
Month = FORMAT('Table'[DATE],"mmm")
Relationship.
Then create a new measure.
TARGET_AJUSTED =
SUMX (
SUMMARIZE (
'TARGET',
'TARGET'[MONTH],
'TARGET'[TARGET],
"max_of_percent", MAX ( 'Table'[PERCENT] )
),
'TARGET'[TARGET] * [max_of_percent]
)
Attached PBIX file for reference.
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |