Can't substract a non working days on calculation

[Sorakell]

Hi everybody!

Today I noticed an error in a formula I thought was working correctly.

I need to count jobs done in the previous day (if today is 17/12 then i need 16/12) but I don't want to count the non working days and since my company operates on saturdays I need 15/12. I did an auxiliary calendar table with excel with the holidays of my region and sundays and did another column with "yes" or "no" to mark the days as "working day".

The formula I use is: 

Cantidad de vehiculos AYER = CALCULATE([Cantidad de vehiculos];DATEADD(Calendario[Date]=TODAY();-1;DAY)

when i try to add the filter to a CALCULATION I get an error (of DAX sintaxis):

Cantidad de vehiculos AYER = CALCULATE([Cantidad de vehiculos];DATEADD(Calendario[Date]=TODAY();-1;DAY);'Calendario FT'[es laborable?]="si"))

The data i use is correct because if in my first formula I substract 2 instead of 1 I get what I want but I'm not able to find a solution. I'm struggling with it trying different formulas and I can't find a solution.

Can sombebody give a hand?

Thanks in advance!

[v-yuezhe-msft]

@Sorakell,

The first parameter in DATEADD function should be a column that contains dates.

Cantidad de vehiculos AYER = CALCULATE([Cantidad de vehiculos];DATEADD(Calendario[Date];-1;DAY)）

Cantidad de vehiculos AYER = CALCULATE([Cantidad de vehiculos];DATEADD(Calendario[Date];-1;DAY);'Calendario FT'[es laborable?]="si")

If the above DAX don't help, please share sample data of your table and post expected result here.

Regards,
Lydia

[Sorakell]

Sorry, but i don't know how to share data.

If you don't mind to explain I could send the excel and .pbix file so anybody who would like to test it could try.

If not I will post some pics...

thanks for your interest.

[AlB]

@Sorakell

You can share the URL to the file if it's on  platforms like Dropbox, OneDrive, etc. or you can upload the file to a site like this (no sign-in required) and post the URL here.  

[Sorakell]

https://drive.google.com/file/d/1Lhc7odBdH1JB3Ra-Emd5rdgQ1-BzI6H1/view?usp=sharing https://drive.goo...

These are the archives I use.

[v-yuezhe-msft]

@Sorakell,

Only a PBIX file is contained in the above link.

Could you please explain what is your expected result based on the sample data? In your PBIX file, please use DATEADD function as below.


Regards,
Lydia

[Sorakell]

Thanks Lydia, but the formula you give me counts all the dates to yesterday thats why I use TODAY -1 because I need the value only of the previous day.

I have another post 

Problem creating a dates column

in there I tried also to post the .pbix and the excel tables but the link doesn't work. In the last reply i try to recreate the process step by step and explaining the best i can what i'm expecting to get. If you don't mind to take a look there maybe it will be more easy.

Sorry,but i'm new in Power Bi and i'm new in this forums and i'm not even able to post a link to the post.

Thanks for your help and interest.

[v-yuezhe-msft]

@Sorakell,

I am not very clear about  what you mean by "I need the value only of the previous day". If you only want to get data of Today()-1, you can simply create the following measure.

CALCULATE([Cantidad de vehiculos]; filter('Calendar';'Calendar'[Date]=TODAY()-1))

Regards,
Lydia

[Sorakell]

Hi Lydia!

I meant that i only wanted the value of one date (only one day). In this case I need the previous working day, so I need to filter in another column to know if previous day was a working date or not.

the formula i use currently is that:

Cantidad de vehiculos AYER = CALCULATE([Cantidad de vehiculos];DATEADD(Calendario[Date]=TODAY();-1;DAY);'Calendario FT'[es laborable?]="si")

The formula works partially, because it counts the production of the day before (correct) but it doesn't filter what was the previous day (incorrect).

Once I get home I will try the formula you are giving me, but I will still need the filter for the non working days.

CALCULATE([Cantidad de vehiculos]; filter('Calendar';'Calendar'[Date]=TODAY()-1))

Thanks a lot!

[v-yuezhe-msft]

@Sorakell,

As my previous reply, the first parameter in DATEADD function should be a column that contains dates. It is not allowed to add Calendario[Date]=TODAY() as first parameter in the following DAX.

 
Cantidad de vehiculos AYER = CALCULATE([Cantidad de vehiculos];DATEADD(Calendario[Date]=TODAY();-1;DAY);'Calendario FT'[es laborable?]="si")

Please change the above DAX to the following and check if you get expected result.

 
Cantidad de vehiculos AYER = if(MAX(Calendario[Date])=TODAY();CALCULATE([Cantidad de vehiculos];DATEADD(Calendario[Date];-1;DAY);'Calendario FT'[es laborable?]="si"))

If you still have issues, please post expected result based on sample data following the guide in this blog below.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,
Lydia

[AlB]

Hi @Sorakell

what error do you get exactly?