Problem with Dates Calculations / This year vs Last Year

[Ciria]

Dears:

I have a problem with Time Intelligence I cannot solve by my own.

Sometimes I am required to provide figures at the end of the month or after the first fortnight. Working on full dates I am not having problems, but the point is when I try to use a smaller table showing only YearMonth or YearQuarter.

PowerBI is summing up all the values for last year, this mean the whole month, not till first fortnight or till first month of the quarter or last date with sales on the present year...

I've tried to use distintcount and countrows functions to figure out how many days with sales have happened, but in the way we work, we could have sales on Monday and Wednesday but nothing on Tuesday, and this sequence may not happen the year before.... what I mean is that being on August 15th doesn't mean to have 15 days of sales....

The use of DATESBETWEEN is not recomendable, because I don't want to use manual dates entering or formula correction each time.

The only easy solution I've found is by using a Slicer, Between-Slider type, and then shortlisting the deadline I wish.

The File here:

https://www.dropbox.com/s/88dtadb0s7m1hpd/Dummy%20Model.pbix?dl=0

Any other idea to handle with this easily?

[v-chuncz-msft · Accepted solution]

@Ciria,

Check if the following link helps.

https://www.sqlbi.com/articles/compare-equivalent-periods-in-dax/

[Ciria]

Hi @v-chuncz-msft

It makes sense.

I will try and I will come back.

Thanks for the support 🙂

[Ciria]

Hi @v-chuncz-msft, Dears all:

I found some free time to put this knowledge to test.

The solution you propose works, but it is important to highlight some points:

• You can't use on tables (as values) the measure you've created following along this tutorial, because it doesn't work. It'll only show results where the Date/Day has sales on previous and current year.
• The measure is able to dismiss the extra dates from periods where the latest date of the month hasn't been reached.
  • Example: Last date with sales, August 14th 2017
  • Slicer: Filtering from August 01st to August 31st 2017
  • Two KPI's defined: Using this measure and using "Total Sales LY" directly on DIVIDE function.
• This means you can only use this calculation/measure for % Calculations or KPI's calculations purposes.

Here the file for testing or commenting:

https://www.dropbox.com/s/88dtadb0s7m1hpd/Dummy%20Model.pbix?dl=0

Awaiting for your comments 🙂