cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## subtract days from QTD formula custom fiscal year

hello

i have this formula that calculates the QTD for every company in my dataset:

QTD Total Sales = CALCULATE(daily_sales_7_day_lag[Total Sales],DATESBETWEEN(Date_Table[Date],EDATE(MIN(Date_Table[Date]),-3),MAX(Date_Table[Date]))),"")

however, one of my companies uses a fiscal year, and i need to adjust the formula so it subtracts two dates from the MIN date. how can i go about doing that, specific to that company?
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: subtract days from QTD formula custom fiscal year

@aali it is best practice to use variable var in DAX to break down the formula and then it is easy to work

```QTD Total Sales =
VAR __minDate = IF( MAX(Table[Company] ) = "ABC", <add your date formula here>, EDATE(MIN(Date_Table[Date]),-3))
VAR __maxDate = MAX(Date_Table[Date])
RETURN
CALCULATE(daily_sales_7_day_lag[Total Sales],DATESBETWEEN(Date_Table[Date],__minDate,__maxDate)),"")```

Proud to be a Datanaut! Kudos Help
Connect with me on Linkedin. Feel free to email me with any of your BI needs.

3 REPLIES 3
Super User

## Re: subtract days from QTD formula custom fiscal year

@aali it is best practice to use variable var in DAX to break down the formula and then it is easy to work

```QTD Total Sales =
VAR __minDate = IF( MAX(Table[Company] ) = "ABC", <add your date formula here>, EDATE(MIN(Date_Table[Date]),-3))
VAR __maxDate = MAX(Date_Table[Date])
RETURN
CALCULATE(daily_sales_7_day_lag[Total Sales],DATESBETWEEN(Date_Table[Date],__minDate,__maxDate)),"")```

Proud to be a Datanaut! Kudos Help
Connect with me on Linkedin. Feel free to email me with any of your BI needs.

Frequent Visitor

## Re: subtract days from QTD formula custom fiscal year

`IF( MAX(Table[Company] ) = "ABC" `

does not work with my data. i have also tried the command

if(contains(company_table,company_table[Company_Name], "ABC")

but the formula does not seem to filter out the companies.

Super User

## Re: subtract days from QTD formula custom fiscal year

@aali share sample data/pbix to further look into it, it is very hard to say why it is not working.