Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Sheila Scott]

Please can anyone help me to calculate Total MTD Sales. I have a sales file and a file with dates loaded into Visual Studio. I have created a relationship between the sales file on Invoice Date, which is in Julian format (CYYDDD) and a similar field in the dates file. There is a column in the dates file which corresponds to the julian date in the format dd/mm/yyyy.
This is the formula I am using :
Measure 1:=TOTALMTD(SUM(FH42044BI[A6$PEP]),FH42044BI[A6IVD],STARTOFMONTH,TODAY())
where A6$PEP is the extended sales value and A6IVD is the Invoice date in Julian format

——————————
Sheila Scott
IT Business Analyst
Hemel Hempstead
+441442458257
——————————

[Andrew Foulk]

Hello Sheila,
Are you asking how to calculate the MTD in Visual Studio or in Power BI?
If in PBI, you can use:

Total Sales = SUM(  ‘sales_table'[sales_column] )

*then using that Total Sales measure you use the below for MTD

Sales MTD =
IF (
    LASTDATE ( ‘*date_table'[date_column] )
        > TODAY (),
    BLANK (),
    CALCULATE ( [Total Sales], DATESYTD ( ‘*date_table'[date_column] ) )
)

The reason I do it like this was learning from Sam McKay (Enterprise DNA) that this works best in Charts because it helps cut off where the results go to when dates are still blank. If you did not want to do this, you would just use the CALCULATE portion of the measure for your MTD.

CALCULATE ( [Total Sales], DATESYTD ( ‘*date_table'[date_column] ) )

I’m hoping since this is a Power BI group you were asking for it in BI and not for in Visual Studio as your subject seems to mention.

——————————
Best Regards,
Andrew Foulk
——————————
——————————————-

[Sheila Scott]

Hi Andrew
Thanks for your response.

I’m creating a data model in Visual Studio, not PowerBI?. Would it make more sense to calculate MTD Sales in PowerBI as that is where we want to display the data, ultimately?

——————————
Sheila Scott
IT Business Analyst
Hemel Hempstead
+441442458257
——————————
——————————————-

[Andrew Foulk]

Hi Sheila,
I would yes to doing all of the calculating within Power BI. Power BI has so much flexibility and capabilities that it would be best in this situation to just bring in your data from any and all sources and then use DAX to compile your calculations. This way you shouldn’t need to worry about any results being skewed due to calculations occurring within another program or source and then doing the same within Power BI.?

The alternative would be is you were using analysis services like Azure AS or SSAS since those would likely be setup to be calculating on the complete data set.

——————————
Best Regards,
Andrew Foulk
——————————
——————————————-

[Sheila Scott]

Hi Andrew

We are using DAX in SSAS. Can you tell me what the formula should be to calculate Total MTD Sales

——————————
Kind regards
Sheila Scott
IT Business Analyst
Hemel Hempstead
+441442458257
——————————
——————————————-

[Hasham Niaz]

Hi Sheila,

Try the same DAX formula mentioned above, you are using Tabular Version of SSAS Cube.

Regards,

——————————
Hasham Bin Niaz
Sr. BI Consultant
Karachi, Pakistan
——————————
——————————————-

[Andrew Foulk]

Hi Sheila, you would should be able to use the same formulas, just replace where it has YTD with MTD. I’m not familiar with SSAS yet myself but I would assume that make no difference on the DAX formulas.?

——————————
Best Regards,
Andrew Foulk
——————————
——————————————-