Solved: CALCULATE filerts acting like OR instead of AND ? - Page 2

josipinho · ‎09-20-2017

Beeing new to Power BI and DAX I encounter lots of questions on a daily basis but this one I cant figure out. I am using CALCULATE to sum up a measure up to current date, and another measure to sum up for same period last year. So I tried using all the built in time inteligence fucntions but they arent doing much because my CALENDAR has dates up to 2 years in the future because there are forecasts involved later on, so filtering those dates isnt an option.

The formula I use for current year is this one:

YTD Kolicina = CALCULATE(SUM(F_Prodaja[KOLICINA]);FILTER(D_VRIJEME;YEAR(D_VRIJEME[DATUM])= YEAR(NOW()) && D_VRIJEME[DATUM] < NOW()))

It works exaclly how I want it to work, it sums up KOLICINA from the begining of the year to exaclly current system date, and each day it is run the date changes to show correct data.

The formula I use for Previous Year (PY) is this one:

YTD PY Kolicina = CALCULATE(SUM(F_Prodaja[KOLICINA]);YEAR(D_Vrijeme[DATUM])=(YEAR(NOW())-1) && MONTH(D_VRIJEME[DATUM])<=MONTH(TODAY()) && DAY(D_VRIJEME[DATUM])<DAY(TODAY()))

So my idea was to filter out only the dates that I want for previous year and sum them up, but what ends up happening is that the dates get filtered out almost like I used OR, not AND.

I created a table that gets filtered by a slicer and another table that doesnt to try and see what was the expected result:

every day that is before the 20th in each month before september is filtered

The bottom table just shows my measure KOLICINA summed up and filtered by previous year dates using a slicer, so I know the total sum that should be returned is 637k, but when i added each date to the top table so I can analyze whats happening I was surprised to see that each day that was AFTER the 20th was removed from the table, so basically I tried to use AND (&&) but I think it acts like OR but I dont understand why...

I tried a lot of different things and im out of ideas, if anyone could help that would be much appreciated

Thanks

CheenuSing · ‎09-21-2017

Hi @josipinho

I did some thing like below for a sales dashboard.

1. Let us say you have a Sales fact table.

2. Have a measure called SalesAmount = sum([SalesinFactTable])

3. Also there is a calendar table called Calendar.

4. For this year to date the measure used is

   SalesThisYear = CALCULATE (
[SalesAmount] ,
    FILTER (
    ALL ( 'MasterCalendar' ),
        'MasterCalendar'[Year] = Year(TODAY())
            && 'MasterCalendar'[Date] <= TODAY()
          ))

5. Created a measure called as LastDatePrevYear = EDATE(TODAY(),-12) . This will give the date one year prior to current date.

6. Created a measure called as FirstDatePrevYear = Date(Year(TODAY())-1,01,01). Assuming Jan is the beginning of the calendar year.

7 Then sales last year for upto the same date from today a year ago is

SalesLastYear =
CALCULATE ( [SalesAmount] , Datesbetween(MasterCalendar[Date], [FirstDatePrevYear],[LastDatePrevYear]) )

This is dynamic and will change based on the run date which is TODAY(). This does not take into consideration the maximum date of your calendar table.

If this solves your problem, please accept this as a solution and also give KUDOS.

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

CALCULATE filerts acting like OR instead of AND ?

Helpful resources

Power BI Monthly Update - April 2024

How to Get Your Question Answered Quickly