This Year vs Last Year

[ricafonyat]

Hello Everyone, I hope all is well with you.

Let me know if you can help me with a matrix that I am trying to build.

In my model there are 2 Tables: Sales and Dates

Sales from Jan 2017 to Nov 2018. So there is no data for dec 2018.

Below you will see the matrix that I am trying to build:

And below it is the one that I got with Dax basic formulas:

As you can see I do not want to consider data from December 2017.

Any help would be very appreciated.

Best Regards
Jose Ricardo

[sam.mckay]

Thanks for your patience.

Check out the technique discussed in the below forum post.

This is exactly how I would solve this so that you only show what you require in your Power BI report page.

Dynamic Date Query Table

What I would do in this case is create you date table to go out far into the future, for example 2020. Then within the date table add a calculated column with logic inside of it that shows up to today (or something like that) Then you will have a column that you can use in the report or page filter area. [image] Think of this like an automatic switch across your report that will produce the filters that you need, like you’re having to do manually at the moment. Here’s an example of the calc…

[ricafonyat]

Thanks Sam!

It works nicely!

I just created a calculated column in Dates Table

=CALCULATE(MONTH(MAX(Sales[Date]));
FILTER(Sales;
not ISBLANK(Sales[Amount])))>=Dates[MonthNumber]

and Sales measure

CALCULATE(SUM(Sales[Amount]);FILTER(Dates;Dates[IsMonthValid?] = TRUE()))

Best Regards