Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Datahungry
Frequent Visitor

DIRECT QUERY equivalent to SAMEPERIODLASTYEAR

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!

1 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

View solution in original post

3 REPLIES 3
GILLOJUNKUMAR
Frequent Visitor

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
Employee
Employee

Hi @Datahungry,

 

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

1.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.