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 all,
I have three tables: rawdata, date and type. In the rawdata, I added a new column whose name is "New Type". Then I linked the "New Type" column to the "type" column in type table.
I want to calculate the last month's amount and the last second month's amount. I know we can use dateadd formula, but that formula does not work when you choose several month and display data as a card.
Below is the formula I used:
Proud to be a Super User!
Solved! Go to Solution.
hi, @ryan_mayu
Please try this formula,
test = VAR maxdate=MAX('Rawdata'[date]) VAR PM1 = DATE(YEAR(maxdate),MONTH(maxdate)-1,01) VAR totalamount=sum(Rawdata[amount]) VAR PM1TOTAL=CALCULATE([totalamount],FILTER(ALLEXCEPT(Rawdata,Rawdata[New Type],'Type table'[type]),'Rawdata'[date]=PM1)) RETURN PM1TOTAL
In your conditional, ALLEXCEPT(Rawdata,Rawdata[New Type]) , so only Rawdata[New Type] can filter the data.
you just add a conditional for 'Type table'[type] in ALLEXCEPT(Rawdata,Rawdata[New Type],'Type table'[type])
Result:
Best Regards,
Lin
Can anyone help me on this? It's really urgent. Thanks in advance
Proud to be a Super User!
hi, @ryan_mayu
Please try this formula,
test = VAR maxdate=MAX('Rawdata'[date]) VAR PM1 = DATE(YEAR(maxdate),MONTH(maxdate)-1,01) VAR totalamount=sum(Rawdata[amount]) VAR PM1TOTAL=CALCULATE([totalamount],FILTER(ALLEXCEPT(Rawdata,Rawdata[New Type],'Type table'[type]),'Rawdata'[date]=PM1)) RETURN PM1TOTAL
In your conditional, ALLEXCEPT(Rawdata,Rawdata[New Type]) , so only Rawdata[New Type] can filter the data.
you just add a conditional for 'Type table'[type] in ALLEXCEPT(Rawdata,Rawdata[New Type],'Type table'[type])
Result:
Best Regards,
Lin
Hi Lin,
It works. Many thanks for your help. Really apprecated that.
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 |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |