SPLY from different sources depending on date

[Anonymous]

Hi there,

I'm trying to build a metric, but need to pull the data to estimate the SAMEPERIODLASTYEAR from different 'Table'[Column] depending on the period of time. Before Jul-2021 the data comes from a spreadsheet and from Aug-21 on, data comes from a database. I tried with the following DAX but without success

REL.TONS SPLY =

VAR SPLY_P6 = SUM('TableZ'[Released (Fiscal Month)])

VAR SPLY_Historic = SUM('TableX'[Total Shipped ])

RETURN

IF(DATESBETWEEN('Date'[Date], DATE(2019,01,01), DATE(2021,07,31)),

CALCULATE(SPLY_Historic, SAMEPERIODLASTYEAR('Date'[Date])),

CALCULATE(SPLY_P6, SAMEPERIODLASTYEAR('Date'[Date]))

)

I got the MSM error message "Can't display the visual"

The expected result is being able to show graphics with the SAMEPERIODLASYEAR like this, comparing a series of data and the result on the same period of time the previous year.  As I mentioned, the respective data is split between two tables with different original sources.

Thanks in advance!

[Anonymous]

Hi  @Anonymous ,

Please try to update the formula of your measure [REL.TONS SPLY ] as below (the part with red font is updated one) and check whether it can return the correct result:

REL.TONS SPLY =
VAR SPLY_P6 =
    SUM ( 'TableZ'[Released (Fiscal Month)] )
VAR SPLY_Historic =
    SUM ( 'TableX'[Total Shipped ] )
VAR _curdate =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    IF (
        _curdate >= DATE ( 2019, 01, 01 )
            && _curdate <= DATE ( 2021, 07, 31 ),
        CALCULATE ( SPLY_Historic, SAMEPERIODLASTYEAR ( 'Date'[Date] ) ),
        CALCULATE ( SPLY_P6, SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
    )

If the above one is not working, please click on "See details" in the error screenshot below, and then send me a screenshot of the detailed error message in the window that will open. Later we will do a troubleshooting to find a solution based on the error details you provided. In addition, please provide us with some sample data with Text fomat and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

Hi,

Although the other option solved my issue, I tried this and would like to explore it as an alternative. The only thing is that when applying your formula it gives me the exact same period as a result

REL. SPLY Not working test =

VAR SPLY_P6 =

SUM ('Table 1'[OM_ACTUAL])

VAR SPLY_Historic =

SUM ('1- RELEASED BEF.JUL-21'[Released (FM)])

VAR _curdate =

SELECTEDVALUE ( '1- MSA-Calendar'[Date] )

RETURN

IF (

_curdate >= DATE ( 2019, 01, 01 )

&& _curdate <= DATE ( 2021, 07, 31 ),

CALCULATE ( SPLY_Historic, SAMEPERIODLASTYEAR ( '1- MSA-Calendar'[Date] ) ),

CALCULATE ( SPLY_P6, SAMEPERIODLASTYEAR ( '1- MSA-Calendar'[Date] ) )

)

[Whitewater100 · Accepted solution]

Hi:

I had this happen too. I did a basic sum of each tables sales (they both were connected to date on the same field like ship date or order date.

DB Sales = SUM(DBSalesTable[sales amt])

Spreadshhet Sales = SUM( Spreadsheettable[Sales amt])

Then I added them together. Total Sales = [DB Sales] + [SpreadsheetSales]

Since both sales tables can be linked to your date table, now it's business as usual.

It looks like you have a separate Date table which is great. It's good to mark it as a date table (Table tools).

Now you can have

 LY Sales = CALCULATE[Total Sales], DATEADD(Dates[Date], -1, YEAR)) or use SAMEPERIODLY

YTD Sales = CALCULATE([Total Sales], DATESYTD(Dates[Date]))

LY YTD Sales = CALCULATE([YTD Sales], DATEADD(Dates[Date], -1, YEAR))

I hope this handles your request!