cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
aali Frequent Visitor
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
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)),"")




Did I answer your question? Mark my post as a solution.

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




 




3 REPLIES 3
Super User
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)),"")




Did I answer your question? Mark my post as a solution.

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




 




aali Frequent Visitor
Frequent Visitor

Re: subtract days from QTD formula custom fiscal year

Hi thank you for your answer,

 

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





Did I answer your question? Mark my post as a solution.

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