Forum Discussion
Convert Excel formula to DAX
Hello,
Can someone please assist with converting this excel formula to DAX
SUMIFS('dispute log'!$K:$K,'dispute log'!$AL:$AL,">"&$A17,'dispute log'!$AK:$AK,"<="&$A17)+SUMIFS('dispute log'!$K:$K,'dispute log'!$AL:$AL,"---",'dispute log'!$AK:$AK,"<="&$A17)
Spelt out:
=SUMIFS(Dispute_Log[dispute_amt],Dispute_Log[Close Period]>MONTH, Dispute_Log[Open Period]<=MONTH)+ SUMIFS(Dispute_Log[dispute_amt],Dispute_Log[Close Period] IS BLANK > MONTH, Dispute_Log[Open Period]<= MONTH)
You can use the following DAX formula to replicate the functionality of your Excel formula:
= VAR Month = MAX(Dispute_Log[Month]) RETURN CALCULATE( SUM(Dispute_Log[dispute_amt]), Dispute_Log[Close Period] > Month, Dispute_Log[Open Period] <= Month ) + CALCULATE( SUM(Dispute_Log[dispute_amt]), Dispute_Log[Close Period] = BLANK(), Dispute_Log[Open Period] <= Month )Make sure to replace the table and column names as necessary. This formula first finds the maximum month value in the Dispute_Log table and assigns it to a variable. Then, it uses the CALCULATE function to calculate the sum of dispute_amt where the Close Period column is greater than the maximum month value and the Open Period column is less than or equal to the maximum month value. The formula then adds to this sum the sum of dispute_amt where the Close Period column is blank and the Open Period column is less than or equal to the maximum month value.
Credit: ChatGPT | https://chat.openai.com/chat
*I did not answer your question, the AI chatbot ChatGPT gave this answer*
1 Reply
You can use the following DAX formula to replicate the functionality of your Excel formula:
= VAR Month = MAX(Dispute_Log[Month]) RETURN CALCULATE( SUM(Dispute_Log[dispute_amt]), Dispute_Log[Close Period] > Month, Dispute_Log[Open Period] <= Month ) + CALCULATE( SUM(Dispute_Log[dispute_amt]), Dispute_Log[Close Period] = BLANK(), Dispute_Log[Open Period] <= Month )Make sure to replace the table and column names as necessary. This formula first finds the maximum month value in the Dispute_Log table and assigns it to a variable. Then, it uses the CALCULATE function to calculate the sum of dispute_amt where the Close Period column is greater than the maximum month value and the Open Period column is less than or equal to the maximum month value. The formula then adds to this sum the sum of dispute_amt where the Close Period column is blank and the Open Period column is less than or equal to the maximum month value.
Credit: ChatGPT | https://chat.openai.com/chat
*I did not answer your question, the AI chatbot ChatGPT gave this answer*