Different result for DAX measure when filtered out only relevant records with COUNTROWS

[soldous]

Hello,

I have a relatively simple measure for calculating differences of volumes between months.

Measure = SUM(Volume) - CALCULATE(SUM(Volume),PREVIOUSMONTH(MonthPeriod))

It works good in general but there are some issues.

I want to see only relevant months so I created a measure in my fact table and applied this measure as a filter on the visual where Rows are greater than or equal 1:

Rows = COUNTROWS(FactTable)

And finally, I have a slicer for year selection. When I choose one year or all it works good but when I choose more years from slicer I receive wrong value on the start of each year (not the delta but only the SUM(Volume). The reason is that CALCULATE(SUM(Volume),PREVIOUSMONTH(MonthPeriod)) is null for the first month of each selected year. I don't know why. For the first selected year, it is understandable but why for the second selected as well? 

Could someone help me, please?

[sanimesa]

You could create a previous month measure without using the previousmonth function. Simply get the month in the context and then subtract 1 from it. You can also test for if the year has been filtered using hasonevalue etc.

[soldous]

Hello, sanimesa,

Thanks for your answer.

I've already tried DATEADD(MonthPeriod,-1,Month) or PARALLELPERIOD(MonthPeriod,-1,Month). ParallelPeriod gives me the same result as PreviousMonth. DateAdd gives me completely wrong results with many blanks.

[Anonymous · Accepted solution]

DATEADD( MonthPeriod, -1, MONTH )? Is MonthPeriod a DATE? If it's not, then you know why it does not work. As I already said, time-intel functions need a date column (without gaps) to work correctly.

Best
D

[soldous]

That's it! I didn't get it on the first time.

Thanks a lot for your help.

Best regards

[Mariusz]

Hi @soldous 

I imagine that PREVIOUSMONTH would return BLANK in the first month as there will be no data, have you investigated what this part of your measure return in these situations?

CALCULATE(SUM(Volume),PREVIOUSMONTH(MonthPeriod))

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[soldous]

I added two pictures for better understanding.

On the first picture, you can see the situation with more selection in the slicer and bad result:

And on the second picture, you can see the situation when all months from slicer are selected. So basically the same situation but with the different and correct result:

Thanks a lot.

[Anonymous]

The answer is obvious if you know how DAX works. By putting a filter on Year, you are calculating your functions with this filter on. You do not take it off at all in your functions. PREVIOUSMONTH works the way you'd expect ONLY IF YOU HAVE A CORRECT DATE TABLE IN THE MODEL. With all dates in it and marked as the Date table. Please do yourself a favor. Create a normal, correct Date table, connect it to your fact, mark it as the Date table in the model and hide the Date column in it (as you don't need it). Then and only then, if you start using time-intel functions with the Date column, you'll get correct answers. Please always use time-intel functions with a PROPER CALENDAR. Do not cut corners.

Best
D

[soldous]

Hi @Anonymous 

Thank you for your feedback.

I changed the date dimension to complete date dimension with date field for each date. The model is in the SSAS and the table is marked as Date Table with this whole date column as a unique identifier.

But the situation with the measure is the same.

[Mariusz]

Hi @soldous 

PREVIOUSMONTH function is a Time intelligence function so it requires Date Dimension or Or Auto Date/Time Hierarchies 

Are you using the Date Dimension / Calendar Table to work correctly? 
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns
https://www.sqlbi.com/articles/automatic-time-intelligence-in-power-bi

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[soldous]

Hello Mariusz,

Well, basically yes. I use a date dimension but not to the day granularity but only to month granularity. We basically have records for each first day of the month. I also set this table as Date Table.

The strangest thing for me is that when I don't filter the visual with COUNTROWS it works. But it also adds one more column to the end where there are no records for the current month but PREVIOUSMONTH gives me the last month. So I need the COUNTROWS filter and then it doesn't work.