SSAS Tabular Model DAX measure in paginated report with incorrect result

[Anonymous]

A paginated Report has a parameter to select a month and year, e.g. March 2021. The following Dax measure through SSAS Tabular Model in a dataset created by Report Builder shows the correct sum:

Value1 : = calculate(sum(table1[value1]))

Another Dax measure should list the sum of dezember of previous year:

Value1 Dez PY : = calculate([Value1], lastdate(previousyear(lastdate(table1[Date]))) )

Table1 contains rows for each product and month like:

Date(mm.yyyy)                   Product                         Value1

12.2020                              A                                    100
03.2021                              B                                    110

In 03.2021 there is no record for product A. The report output should looks like this:

Product                Selected Month              Last Month Previous Year                   

B                          110                                                                                             

A                                                                100                                                        

Actually the report does not show the product A and value for Last Month Previous Year

Product                Selected Month              Last Month Previous Year                   

B                         110                                                                                              

Any idea how to get also Product A which is not available in 03.2021?

[d_gosbell]

The issue you are having is caused by the fact that you are not using a calendar table. Time Intelligence functions like PreviousYear are designed to work with a calendar table that had a contiguous range of date values (with no gaps). If you don't already have one of these in your data model you can create a basic one in DAX (see https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/)

If you add a table like this then related it to the date column in Table1 (I would then also suggest hiding this column in Table1 so that no one uses it accidentally) then use the [Date] column from this new table in your PreviousYear expression it should start to work.

Sometime the date functions will work in table like Table1 but the results will often not be consistent depending on what other filters are active. The other issue is that you are only showing 12.2020 the actual data would need to be the 31.12.2020 (dd.mm.yyyy) for the LastDate function to find any data.