Strange behaviour of Power Bi vs Excel

[Kyuss82]

Hi,

I'm a new user of Power Bi and i have a strange problem with a data filter applied to a measure that calculate a cumulative (monthly/year etc). My architecture is: SSAS in SQL Server 2012 sp4 and Power Bi as frontend.

In SSAS I've created a formula like this:

Cumulative Previous Year:=CALCULATE (
    SUM([TOTAL VOLUME]) ;
    FILTER (
       ALLSELECTED ('FACT TABLE');
        'CALENDAR TABLE'[YEAR]
            = (YEAR(  MAX('FACT TABLE'[DAY]))-1)&&'CALENDAR TABLE'[MONTH]<=(MONTH(MAX('FACT TABLE'[DAY])))&&'CALENDAR TABLE'[DAY]<=DAY(MAX('FACT TABLE'[DAY]))
    )
)

 This formula work as espected in Excel but give an empty result in Power Bi when for example a year is selected from a data filter.

What am I doing wrong?

Thanks

[Pragati11]

Hi @Kyuss82 ,

Can you please put some screenshots from Power BI on what result this DAX expressions is giving? The expression looks right to me.

Thanks,

Pragati

[Kyuss82]

Hi @Pragati11 ,

Thanks for your reply.

Here's the report in Power Bi without a filter

Power bi without filter

Here's using the filter

The same model using Excel

Thanks

[Pragati11]

Hi @Kyuss82 ,

Try modifying your dax as follows:

Cumulative Previous Year:=CALCULATE (
SUM([TOTAL VOLUME]) ;
FILTER (
ALLSELECTED ('FACT TABLE');
'CALENDAR TABLE'[YEAR] = (YEAR( MAX('FACT TABLE'[DAY]))-1)&&'CALENDAR TABLE'[MONTH]<=(MONTH(MAX('FACT TABLE'[DAY]))) && ('CALENDAR TABLE'[DAY]<=DAY(MAX('FACT TABLE'[DAY])))
)
)

Just added 2 brackets in your DAX.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Kyuss82]

Thank you for your prompt reply @Pragati11 !

Unfortunately i've got the same result 😞

I'm starting to think that the problem is SSAS 2012 that maybe is too old to work correctly with Power BI...

[Kyuss82 · Accepted solution]

Ok found a solution for my problem

Using a formula like this:

Cumulative Previous Year:=CALCULATE (
    SUM([TOTAL VOLUME]) ;
    FILTER (
       ALL ('CALENDAR TABLE');
        'CALENDAR TABLE'[YEAR]
            = (YEAR(  MAX('FACT TABLE'[DAY]))-1)&&'CALENDAR TABLE'[MONTH]<=(MONTH(MAX('FACT TABLE'[DAY])))&&'CALENDAR TABLE'[DAY]<=DAY(MAX('FACT TABLE'[DAY]))
    );ALL('FACT TABLE'[DAY])
)

Now everything work as espected 😉

Regards