Need help with DAX

[femcoldt]

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

[femcoldt]

HI @v-shex-msft 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

[Ashish_Mathur]

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.

[femcoldt]

Hi @Ashish_Mathur 

Find the link to the excel dummy excel_dummy.xlsx

Thank you for your help

[Ashish_Mathur]

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.

[femcoldt]

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

[Ashish_Mathur]

Hi,

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

Hope this helps.

[femcoldt]

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

[Ashish_Mathur]

Share the download link of the MS Excel file again.

[femcoldt]

Hi @Ashish_Mathur . Here it is excel_dummy.xlsx

[Ashish_Mathur]

Hi,

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

[femcoldt]

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

[Ashish_Mathur]

Hi,

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

[v-shex-msft]

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