Issue with connecting an external source with previous year's info!

[rcb0325]

I am trying to calculate previous year's sales from an external source, and show it as a 'last year to date' calculation. 

The main dataset holds this years sales info, and I have a [Sales Amount YTD] calculation that is working correctly. I am having a hard time summing the sales amount in the external data source (excel file), and showing that total as [Sales Amount LYTD]. The value shown as the LYTD in the table is the total for all of 2023, and not just 01/01/2023-01/23/2023. 

I cannot create a hard relationship inside of the data model because of other current relationships, so I have been trying to use TREATAS. 

Below are a couple of pictures. One is just the current table, showing the incorrect LYTD total, and the other is of the data tables. 'PostingDate' is the main dataset, and 'Mfg Rep Sales' is the external excel file housing 2023 info.  

[rcb0325]

@Anonymous , I misread your formula. It appears to be working this way! 

Sales_Amount_LYTD =

CALCULATE( SUM( 'Mfg Rep Sales'[Invoiced Amount]),

YEAR('Mfg Rep Sales'[Invoice Date]) = YEAR(SELECTEDVALUE('dim PostingDate'[PostingDateDate])) -1 && MONTH('Mfg Rep Sales'[Invoice Date]) = MONTH(SELECTEDVALUE('dim PostingDate'[PostingDateDate])) && DAY('Mfg Rep Sales'[Invoice Date]) = DAY(SELECTEDVALUE('dim PostingDate'[PostingDateDate]))

)

Thank you for your help. 

[rcb0325]

@Anonymous here is the calculation for the current [Sales_Amount LYTD] 

Sales_Amount LYTD =

CALCULATE( SUM( 'Mfg Rep Sales'[Invoiced Amount]),

PARALLELPERIOD( 'dim PostingDate'[PostingDateDate], -1, YEAR),

TREATAS( VALUES('Mfg Rep Sales'[Invoice Date]), 'dim PostingDate'[PostingDateDate])

)

I cannot provide the calculation for the [Sales Amount YTD] column, because of data security reasons. 

In your calculation, I cannot insert the 'Invoice Date' column from the 'Mfg Rep Sales' table, as if it isnt allowed. Tyring to mark the column as a date table, I get an error of 'column must have unique values' and 'column cant have gaps'. Any suggestions? 

[Anonymous · Accepted solution]

Hi @rcb0325 ,

Can you provide the DAX you used for your calculations?
Without data and DAX, for the problem you are facing, I can only suggest you to use this filter condition in DAX when calculating the sum of the corresponding time period last year:

YEAR('DATE'[Date]) = YEAR(SELECTEDVALUE('DATE'[Date]) - 1 && MONTH('DATE'[Date]) = MONTH(SELECTEDVALUE('DATE'[Date]) && DAY('DATE'[Date]) = DAY(SELECTEDVALUE('DATE'[Date])

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rcb0325]

@Anonymous are you able to help me be able to sum the LYTD totals by company? Since the [Posting Date] values are not in this particular table, the measure will not work properly. It will work if I remove the [Company] column, and add in [Posting Date].

Sales_Amount_LYTD =

CALCULATE( SUM( 'Mfg Rep Sales'[Invoiced Amount]),

YEAR('Mfg Rep Sales'[Invoice Date]) = YEAR(SELECTEDVALUE('dim PostingDate'[PostingDateDate])) -1 && MONTH('Mfg Rep Sales'[Invoice Date]) = MONTH(SELECTEDVALUE('dim PostingDate'[PostingDateDate])) && DAY('Mfg Rep Sales'[Invoice Date]) = DAY(SELECTEDVALUE('dim PostingDate'[PostingDateDate]))

)