DIRECT QUERY equivalent to SAMEPERIODLASTYEAR

[Datahungry]

Hi everyone,

I'm using a SQL DB (DirectQuery  mode) as a datasource for my report.

I have a DateTable and SalesTable.

My goal is to have a report with:

- 2 slicers: 1 for years; 1 for months

- 1 List visual with 3 columns: YearMonth, Sales Amt, SalesAmt Previous Year

I tried the following, according to some examples I found online:

Total SalesAmt PY  =
    CALCULATE(
        [Total SalesAmt],
        FILTER(
            ALL(DateTable[Date Value]),
            AND(
                DateTable[Date Value]>=EDATE(MIN(DateTable[Date Value]),-12),
                DateTable[Date Value]<=EDATE(MAX(DateTable[Date Value]),-12)
            )
        )
    )

The problem is that, if I select more than one non-consecutive month (let's say, Feb and May), since the formula uses MIN and MAX, the result of this formula will include Feb, Mar, Apr and May.

Could someone please let me know how to achieve this?

Thanks!

[GILLOJUNKUMAR]

HI Friends,

I have imported data from oracle databse via import mode. I have created a matric table region wise for month  (eg: Aug'22) i want to display the same for previous year same month (eg: Aug'21)
i used sameperiodlastyear syntax but i getting message "Can't display the visual" can anyone help me in this regards

Thanks

[v-yulgu-msft]

Hi @Datahungry,

You could enable the highlighted option to use SAMEPERIODLASTYEAR() in Direct Query mode.

Best regards,

Yuliana Gu

[Datahungry · Accepted solution]

Thank you for the suggestion, I actually had enabled it already.

I resorted to using a more complex DAX formula because I was trying to find a workaround for the "Power bi expects a contiguous selection" issue.

However, it seems that, if I change the relationshipt between the Date dimention table and Sales from direction from "Both" to "Single" it works.

Best regards