topic Re: Calculate Total USD amount per Month in DAX Commands and Tips

Calculate Total USD amount per Month

feralvarez994 — Thu, 04 Nov 2021 16:24:46 GMT

Hey! I need some help with a DAX Formula. I have a table with the variaton of the price of the USD respect to ARS (Peso from Argentina) and I have to find out how much money we have spent per month in the USD price of the month that happened. I have 3 main tables (calendar, usd price per working day, and salaries table)

What i need to do is to sum the "Costo Total (Total Cost)" of each month, divide each one of them per the price of the USD in that month, and summarize it all to put it in a card in my dashboard as "Total Cost To Date".

In some cases, i do not have the first date of each month with a price value, so i need to use the first date next to it if theres nothing in the first day of the month

Re: Calculate Total USD amount per Month

lbendlin — Sat, 06 Nov 2021 02:05:29 GMT

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

Re: Calculate Total USD amount per Month

v-cazheng-msft — Tue, 09 Nov 2021 07:00:25 GMT

Hi feralvarez994

Based on the information from you, I think you may try this Measure.

Total Cost to Date =

VAR month_total =

CALCULATE (

SUM ( 'salaries table'[COSTO TOTAL] ),

ALLEXCEPT ( 'calendar', 'calendar'[Month] )

)

VAR min_ =

CALCULATE (

MIN ( 'usd price per working day'[Date] ),

FILTER(ALL('calendar'),'calendar'[Month]=MAX('calendar'[Month]))

)

VAR month_usd =

CALCULATE (

SELECTEDVALUE ( 'usd price per working day'[Purchase] ),

'usd price per working day'[Date] = min_

)

RETURN

month_total/month_usd

The result looks like this:

Attached the pbix file as reference.

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it, please feel free to let us know. Thanks a lot!

Re: Calculate Total USD amount per Month

feralvarez994 — Tue, 09 Nov 2021 15:24:59 GMT

Hi v-cazheng-msft and lbendlin , i feel more than grateful for your help, but i couldnt reach the value that i was looking for.

I have tried with the measure thar v-cazheng-msft post but dont know why it doesnt give me any value, so i will attach de PBI file in this comment so you can check this out if you can. Thanks for your help by the way!

I have 4 pages, the one that im trying to reach the value is the last one (Costos Totales USD). The expected outcome has to be the sum of "Costo Total" of each month, divided by the First value of "Precio Compra" of each month.

Sueldos - PBI FILE

Re: Calculate Total USD amount per Month

lbendlin — Wed, 10 Nov 2021 00:25:10 GMT

You may want to modify your data model

- Eliminate the 1:1 relationships by merging the tables

- correct the employees to sales relationship to 1:M with single direction

- Decide what to do with the dangling Summarize table

- You can also decide to create a new "yearmonth" column in the Dolar and Sueldos tables and use that for the FX rate computation. No need to go via the calendar for that.

Re: Calculate Total USD amount per Month

v-cazheng-msft — Thu, 18 Nov 2021 03:02:40 GMT

Hi feralvarez994

Please make a little bit change to your dax formula.

Total Cost to Date =

VAR month_total =

CALCULATE (

SUM ( Sueldos[COSTO TOTAL] ),

ALLEXCEPT ( Calendario, Calendario[Mes] )

)

VAR min_ =

CALCULATE (

MIN ( Dolar[Fecha] ),

FILTER(ALL(Calendario), Calendario[Mes] =MAX(Calendario[Mes]))

)

VAR month_usd =

CALCULATE (

SELECTEDVALUE ( Dolar[Precio Compra] ),

Dolar[Fecha] = min_

)

RETURN

month_total/month_usd

In your original formula, you used Dolar[Compra] to compare with a minimum Dolar[Fecha], which is not supported due to their different data types.

Also, attached the pbxi file as reference.

Best Regards,

Community Support Team _ Caiyun