Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Power BI enthusiasts,
my name is Andreas and I am totally new to this community. I just started to use Power BI Desktop and made so far good progress with video tutorials and hands on practice. I want to work with monthly actual cost data and therefore also need to determine which is the highest month of the current year that carries costs. In my testfile this is march, so numeric a 3. But since I do have also 2016 costs I firstly need to determine the maximum year with actuals. This I found out this evening:
works: MaxYear = CALCULATE(MAX(ds[Year]);filter(ds;[Act]<>0))
This formula returns 2017 which is correct. In a second step I want to determine the maximum month in 2017 that carries cost and used:
does not work: MaxMonth = CALCULATE(MAX(ds[Month]);filter(ds;[Year]=[MaxYear]);filter(ds;[Act]<>0))
So I used the result 'MaxYear' in a second measure. But it does not work! If I hardcode the year as 2017 instead of [MaxYear] it DOES work!
works: MaxMonth = CALCULATE(max(ds[Month]);filter(ds;[Year]=2017);filter(ds;[Act]<>0))
What is the reason for this seldom behaviour? Any ideas? I am nearly desperate tonight, because I made such good progress and do fail now without a clue why.
And one more question: How many 'filter' parameters can be combined in the 'calculate' formula?
Many thanks in advance for your attention and helpful answers!
Best regards, Andreas
Solved! Go to Solution.
Hi, that's great that you have a little a piece of my country with you.
I have a alternative to solve your question
Create a measure :
MaxMonth = VAR Maxyear = CALCULATE ( MAX ( AndreasTable[Year] ), FILTER ( VALUES ( AndreasTable ), CALCULATE ( SUM ( AndreasTable[Act] ) <> 0 ) ) ) RETURN CALCULATE ( MAX ( AndreasTable[Month] ), FILTER ( AndreasTable, AndreasTable[Year] = Maxyear && AndreasTable[Act] <> 0 ) )
hi buddy, please try with this DAX Measure
MaxMonth = CALCULATE ( MAX ( DS[Month] ), FILTER ( DS, DS[Year] = MAX ( DS[Year] ) && DS[Act] <> 0 ) )
Dear Vvelarde,
unfortunately this does not work. It returns nothing. But I assume the line 'MAX ( DS[Year] )' should be 'MAX ( DS[MaxYear] )' ? If I correct it to this it is not acceptex by Power BI.
Any other Ideas?
Thank you & Best regards, Andreas
Hi, to get a correct answer maybe you need to specify the structure of your table.
In test environment i create something like this:
If you have a different structure, please post sample data or a link to test PBIX.
Dear Vvelarde,
you're right. I need to make sure we are talking about the same structure. So I did load your data into Power BI and it worked. Then I compared it to my data and looked for differences. The difference is that I also do have the year 2018 with 0 in the [Act] column, but they are there because in 2018 I might have a forecast. So I added similar 2018 rows with a valid [Month] entry, but no [Act] entry and then the DAX formula did not calculate anymore.
Year | Act | Month |
2016 | -1 | 1 |
2016 | -2 | 2 |
2016 | 54 | 3 |
2016 | 54 | 4 |
2016 | 54 | 5 |
2016 | -341634,6 | 6 |
2016 | 54 | 7 |
2016 | 54 | 8 |
2017 | -21524 | 1 |
2017 | 54 | 2 |
2017 | 0 | 3 |
2017 | -5 | 4 |
2017 | -5,123 | 5 |
2017 | 0 | 6 |
2017 | 0 | 7 |
2018 | 0 | 1 |
2018 | 0 | 2 |
2018 | 0 | 3 |
2018 | 0 | 4 |
Maybe I did not explain it correctly in the first instance... So I am looking for the MAX year that has Actuals <> 0 and within this year the MAX Month.
I tested it back and forth. As soon as 2018 is in, your formula refuses. Otherwise it works perfect! But why?
I am so grateful that you read my post and that now the difference is figured out. But for today I am exhausted. I am from Berlin and here it is 11 p.m. I would be so grateful If you could invest once more some of your experience to solve this. The difference in the formulas will show me a lot with regards to the logic of DAX.
Thank you very much! Best regards from Berlin, Andreas
PS: You are from Lima? I was 2014 in Peru and bought a ring in Cusco handcrafted out of a Soles coin. Whenever I wear it, it reminds of one of my best times ever...
Have a good night later on!
Hi, that's great that you have a little a piece of my country with you.
I have a alternative to solve your question
Create a measure :
MaxMonth = VAR Maxyear = CALCULATE ( MAX ( AndreasTable[Year] ), FILTER ( VALUES ( AndreasTable ), CALCULATE ( SUM ( AndreasTable[Act] ) <> 0 ) ) ) RETURN CALCULATE ( MAX ( AndreasTable[Month] ), FILTER ( AndreasTable, AndreasTable[Year] = Maxyear && AndreasTable[Act] <> 0 ) )
Dear Vvelarde,
It works! It works! It works! Yes! Thank you so much... I need to investigate more on DAX commands tomorrow. I did not expect it would be so complicated in the end... But you rocked it!
I would like to buy you a 'Cusquena Roja', which was my favourite...
Thank you once more & Have a good night!
Best regards, Andreas
Hi @Andreas_H,
Congratulations, you have resolved your issue, pleas mark the right reply as answer, so that more people will find the workaround easily.
Thanks,
Angelia
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |