Power BI DAX

[Anonymous]

=num( sum({<forecast_type={'dispatches-fcst_ship'},cm={'1'},intercompany_flag=,fiscalmonth=,fiscalquarter=,fiscalyear=>}FORECAST_AMOUNT) ,'#,##0') 

What will this QlikView calculation be in Power BI? 

[harshnathani]

Hi @Anonymous ,

Try this measure

Measure= 

var a = CALCULATE (SUM (Table [FORECAST AMOUNT]) , FILTER (Table, Table [Forecast_Type] = "dispatches-fcst_ship" && Table [CM] = 1))

RETURN

FORMAT(a,"#,##0")

Pull the Date from the Date Table and this measure.

The row context will take care of the sums by Fiscal Dates.

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hello,

What do you mean by 'Pull the Date from the Date Table and this measure'? 

[harshnathani]

Hi @Anonymous ,

Drag the column and measures to the Visualization Pane.

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hello,

I understand that but why will I need to add this 'Table [CM] = 1))' into the calculation?

Thanks for you help so far!

[harshnathani]

Hi @Anonymous ,

Is CM a Column in your table

Are you not calculating amount where CM = 1.

If not , please explain what your Qlik function is doing.

Thanks

HN

[Anonymous]

I have used your calculation for myself and this is what I have done. There is an error which is why I was not sure why that was needed. 

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

It is because that you are filtering "fact_all" table,but you wanna get the {cm} value which is in "Master_Calendar" table,you'd better use "lookupvalue()"function or "related()" function.

Below are the references:

https://dax.guide/lookupvalue/

https://dax.guide/related/

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[v-kelly-msft]

Hi @Anonymous ，

What does "cm={'1'}" refer to in your original post?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

I think cm is calendar month but abbreviated to cm. But I am not 100% certain what cm refers to in the original post. 

[v-kelly-msft]

Hi  @Anonymous ,

Better first confirm cm,if it refers to a column,I think what suggested by @harshnathani  is a right solution.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

CM is a column in the master_calendar table as you can see