The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Community,
I have question reqarding function MAX. I have 2 dates in my report: close date and billing date. Close date is column with dates, billing date I calculate with formula below:
IF(AND(MONTH('Acc_Opp'[closedate]+21)=1,
YEAR('Acc_Opp'[closedate]+21)=2018),DATE(2017,12,1),
DATE(2017,MONTH('Acc_Opp'[closedate]+21),1)))
But when I'm trying to use this calculation in measure in fuction MAX I receive message that MAX can only use a column as value. My question is how I can get around this limitation or maybe there is other solution? (I've tried, LastDate is not working in this case).
I can't create billing date as a calculated column because I need to use close date as slicer in 2 different visual plots and I have other table were close date is used for relationship.
My Measure
0test_b.Won_cumulative_billing = CALCULATE(SUM(Acc_Opp[new_runrate]), FILTER(FILTER(ALLSELECTED(Acc_Opp),Acc_Opp[Opp-ty Status] = "1.Won"), (IF(AND(MONTH('Acc_Opp'[closedate]+21)=1,YEAR('Acc_Opp'[closedate]+21)=2018),DATE(2017,12,1),
DATE(2017,MONTH('Acc_Opp'[closedate]+21),1))) <= MAX(IF(AND(MONTH('Acc_Opp'[closedate]+21)=1,
YEAR('Acc_Opp'[closedate]+21)=2018),DATE(2017,12,1),DATE(2017,MONTH('Acc_Opp'[closedate]+21),1)))))
I
Hi @bsas,
Try to add billing date as a calculated column. It won't affect other columns. You can post your issue here if you encounter it.
0test_b.Won_cumulative_billing = CALCULATE ( SUM ( Acc_Opp[new_runrate] ), FILTER ( FILTER ( ALLSELECTED ( Acc_Opp ), Acc_Opp[Opp-ty Status] = "1.Won" ), ( IF ( AND ( MONTH ( 'Acc_Opp'[closedate] + 21 ) = 1, YEAR ( 'Acc_Opp'[closedate] + 21 ) = 2018 ), DATE ( 2017, 12, 1 ), DATE ( 2017, MONTH ( 'Acc_Opp'[closedate] + 21 ), 1 ) ) ) <= MAX ( [Billing Date] ) ) )
Best Regards!
Dale
Thank you @v-jiascu-msft,
Already done as calculated column, but there is problem I need to use 2 different dates in 2 plots, one is close date, second is billing date. And if I use billing date instead of close date other data (tables) which are linked by date to close date ane not working correctly.
I think about dublicating data (split into 2 tables) and use with responsible plot.
Hi @bsas,
Did you use two slicers? They interact with each other. If so, you can try this function as the picture shows.
Best Regards!
Dale
Hi @v-jiascu-msft,
I use one slicer, but problem is not in slicer but in column which I use for visual. Close date <> Billing date, so relationships from different table can't be linked to close date and billing date as they are in 1 table. Thats why I'm trying not to create billing date as calculate table but somehow transfrom close date to billing date in measures to use in calculations.
Hi @bsas,
Glad you solved it. Just a discussion, if the scenario is one table will connect to two columns of another table, we can use function USERELATIONSHIP.
Best Regards!
Dale
Hello, Try using a new column rather than a new measure.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
139 | |
110 | |
107 | |
76 | |
62 |
User | Count |
---|---|
270 | |
129 | |
123 | |
100 | |
92 |