topic Need help with DAX in Desktop

Need help with DAX

femcoldt — Tue, 28 Nov 2023 15:13:08 GMT

Hi everyone, I am in a situation. the screen shot below is in excel, i have all the measures except for the ones in the red rectange. I am fighiting to get a way to calcualte this in powerbi.

the YOY in excel is calculated as =SUM(K8/K3)-1

Quaterly YOY is calculated as =(SUM(K8:M8)/SUM(K3:M3))-1

YTD/YTG is calculated as =(SUM(K8:M8)/SUM(K3:M3))-1.

Any help is appreciated

Re: Need help with DAX

Anonymous — Thu, 30 Nov 2023 08:12:15 GMT

Hi femcoldt ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

BTW, power bi did not exist row and column index, so you can't simply calculate across multiple fields or to accurately calculate on specific cells. You need to use different field as filter to limit the calculate ranges. (notice: recursion calculation not supported in Dax)

Regards,

Xiaoxin Sheng

Re: Need help with DAX

femcoldt — Sun, 03 Dec 2023 23:16:44 GMT

HI Anonymous Xiaoxin,

FInd attached a sample PBIX file from this link Master.pbix with some random dataset about my thoutght process.

I want to to the first three month in the year for F24 Act + Fcst divided by F24 Actual whihc is Jan to March and so on.

Thanks

Re: Need help with DAX

Ashish_Mathur — Sun, 03 Dec 2023 00:05:39 GMT

Hi,

Share some dummy data in an Excel file and on another tab, show the expected results with your formulas. I will try to translate those Excel formulas in the DAX language. Share only as much data as is required to get those 3 calculations.

Re: Need help with DAX

femcoldt — Sun, 03 Dec 2023 23:17:30 GMT

Hi Ashish_Mathur

Find the link to the excel dummy excel_dummy.xlsx

Thank you for your help

Re: Need help with DAX

Ashish_Mathur — Sun, 03 Dec 2023 23:31:10 GMT

There is no concept of cell merging in visuals. Therefore, the workaround would be to insert quarter and half year columns and show the ro 14 and 15 calcultions under those columns. If you are OK with my approach, then share the download link of the PBI file.

Re: Need help with DAX

femcoldt — Mon, 04 Dec 2023 03:37:17 GMT

Hi Ashish_Mathur , thanks. I already attached the PBIX file link already. Here it is again

Master.pbix

https://1drv.ms/u/s!Arru7WEvbiAovkneIrRXoKExUcwn?e=JDoQeJ

Re: Need help with DAX

Ashish_Mathur — Mon, 04 Dec 2023 04:36:01 GMT

Hi,

PBI file attached. All the growth rates appear in 1 row itself (YoY).

Hope this helps.

Re: Need help with DAX

femcoldt — Mon, 04 Dec 2023 14:44:43 GMT

Hi Ashish_Mathur , thanks so much for your help. This is well appreciated.

I still cant fid the values for the YTD/YTG from the results or am i looking at the figures wrongly

Thanks

Re: Need help with DAX

Ashish_Mathur — Mon, 04 Dec 2023 23:03:58 GMT

Share the download link of the MS Excel file again.

Re: Need help with DAX

femcoldt — Tue, 05 Dec 2023 02:04:18 GMT

Hi Ashish_Mathur . Here it is excel_dummy.xlsx

Re: Need help with DAX

Ashish_Mathur — Wed, 06 Dec 2023 00:15:29 GMT

Hi,

Why is the YTD/YTG calculation from Jan-Aug/Sep-Dec?

Re: Need help with DAX

femcoldt — Wed, 06 Dec 2023 13:42:48 GMT

Hi Ashish_Mathur it is a business metric that the business uses. I dont know why they do that

Re: Need help with DAX

Ashish_Mathur — Wed, 06 Dec 2023 22:49:16 GMT

Hi,

Unless i know the logic of the month selection, i cannot help.