Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
josipinho
Helper II
Helper II

CALCULATE filerts acting like OR instead of AND ?

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 filteredevery 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

 

1 ACCEPTED SOLUTION

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

11 REPLIES 11
Ashish_Mathur
Super User
Super User

Hi,

 

I believe those 2 formulas can be simplofied to

 

=CALCULATE(SUM(F_Prodaja[KOLICINA]);DATESYTD(D_VRIJEME[DATUM]);"31/12"))

For previous year

 

=CALCULATE(SUM(F_Prodaja[KOLICINA]);PREVIOUSYEAR(D_VRIJEME[DATUM]))

 

 

 

 

 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I tried that but it doesn't do what i need.

 

What I need is a measure that calculates AMOUNT (KOLICINA) from begining of the year up until the current date that the report is being run.

Then I need that same measure but for previous year. 

 

I tried using built in time inteligence functions but they are very very limited and I didn't manage to acomplish my result using those functions so I tried making my own even if they are not optimal.

 

 

Hi,

 

Share the link from where we can download your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

uploaded it here 

 

https://ufile.io/qr0ob

 

it is a bit messy now because I tried a whole lot of stuff :S

Hi,

 

I do not understand the foreign language but there is what i tried.  I created a mesure in the F_Prodaja table

 

=CALCULATE(SUM(F_Prodaja[KOLICINA]),DATESYTD(D_Vrijeme[DATUM],"31/12"))

This will calculate the quantity from January 1 to the dat which is selected in the Date appearing the Page level filter (see image below)

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you but it needs to be automatized without filtering, so that my client can just turn it on and not have to think about anything but have the data ready each day he runs the report.

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!

Thank you so much, this is the solution I've been looking for!

Hi,

 

In that case, set relative date filtering to show value for this day (see Page level filters) in the image below

 

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-chuncz-msft
Community Support
Community Support

@josipinho,

 

It would be better to simplify your model and show us the expected result with dummy data.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I have a Time dimension, D_Vrijeme, that has all dates from 2015 to 2018, like a Calendar, the dates are continuos.

 

I have a Fact table that has KOLICINA measure (translated that would be quantity), and I have resellers and articles dimensions.

 

I want to create a drilltrough page so that users can drill trough on each reseller and look at how his current year is going, so thats why i want to use year to date quantity and compare it to that same period last  year.

 

For example if the user runs my report on date 21.9.2017, I want to show him the current quantity sold from 1.1.2017 to 21.9.2017 and another collumn next to that one that shows quantity sold from 1.1.2016 to 21.9.2016.

 

If next month my user runs that same report on the date of 15.10.2017, then I want my measure to show him quantity sold from 1.1.2017 to 15.10.2017, and 1.1.2016 to 15.10.2016 in another collumn.

 

It works for current year, but I cant do any more complex calculations with dates because power bi wont allow it. I tried dateadd(TODAY(),-1,year) but it isnt allowed. 

I tried with time intelligence functions but because i have dates up to 2018 it is messed up, and I cant filter years because I have forecasts aswell. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.