cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GerryHeyrman
Frequent Visitor

SUMIFS / DAX combined with dynamic dates

Hi Everyone,

 

I am struggling with converting a (dynamic) SUMIFS formula towards Power BI, formula is summarizing the contract amount of sales opportunities based on multiple (date)-criteria (latest date of each quarter), which is generating the following result:

 

SUMIFS.JPG

I am able to calculate this in Power BI for one specific date, in the following example being 31/12/2020:

Power BI SUMX.JPG

 

Anybody an idea how I should write this measure in DAX, so I would be able to show the calculation for each (latest) date of each quarter as of (f.E.) 01/01/2019 up untill .. ?

 

Many thanks for looking into this !

 

Kind regards,

 

Gerry

1 ACCEPTED SOLUTION

Hi all,

 

I am not really sure how to do that via the date table & have solved this (inefficiently as following):

 

(1) Creating measures for each date (end of quarter), with following formulas (example shown sales pipeline for 30/06/2020): "Sales Pipeline: 30/06/2020 = (sumx(filter(Sales_Opportunities,Sales_Opportunities[CreatedOn]<=date(2020,6,30)&&Sales_Opportunities[ClosingDate]>=date(2020,6,30)),[Sales Contract amount in €])+sumx(filter(Sales_Opportunities,Sales_Opportunities[CreatedOn]<=date(2020,6,30)&&Sales_Opportunities[ClosingDate]=0),[Sales Contract amount in €]))/1000"

(2) generation following result:
Result Power BI.JPG

Despite this being an inefficient approach, I am not able to visualise this into other visuals than tables, as the time dimension is not being used properly.

Anybody an idea how to solve this more efficient (in 1 measure, but having the time dimension seperately ?)

For everyones information, I will try to describe my case more thorougly:

Based on opportunities in our CRM tool, I want to show the sales pipeline (amount in sales phase on a specific date) and I use following checks to calculate this from past dates:

(1) Creation date, which should be before date X

(2) End date, which should be blank or after date X

For the moment I have following (DIM_Date) table:
datetable.JPG

Which is having a relation with the CRM date (Sales_opportunities):

 

Relationship date table & sales opportunities.JPG

If I try to change the formula, from hard coded dated towards the Date field onder Date table (Dim_Date) I get following error:

 

Wrong formula Dim_Date.JPG

Many thanks for looking into this.

 

Kind regards,

 

Gerry

View solution in original post

5 REPLIES 5
lbendlin
Super User
Super User

- Have a Calendar table as part of your data model, including the YearQuarter column

- use LASTDATE() DAX function to find the last date of the quarter

- calculate the value for that date.

- display in the chart.

Hi Everyone,

 

Found the solution using a what-if parameter & putting this into a variable, enabling a dynamic formula.

 

Kind regards,

 

Gerry

Hi,

 

Thanks for your swift reply, for the moment I have following Date table:
datetable.JPG

When I try to convert the yearquarter column from text to date I am not able to.

Any help on the formula for creating a yearquarter column which is convertible towards a date ?

 

Many thanks !

 

Kind regards,

 

Gerry

There is no need for that. You link the table via the Date column, right?

Hi all,

 

I am not really sure how to do that via the date table & have solved this (inefficiently as following):

 

(1) Creating measures for each date (end of quarter), with following formulas (example shown sales pipeline for 30/06/2020): "Sales Pipeline: 30/06/2020 = (sumx(filter(Sales_Opportunities,Sales_Opportunities[CreatedOn]<=date(2020,6,30)&&Sales_Opportunities[ClosingDate]>=date(2020,6,30)),[Sales Contract amount in €])+sumx(filter(Sales_Opportunities,Sales_Opportunities[CreatedOn]<=date(2020,6,30)&&Sales_Opportunities[ClosingDate]=0),[Sales Contract amount in €]))/1000"

(2) generation following result:
Result Power BI.JPG

Despite this being an inefficient approach, I am not able to visualise this into other visuals than tables, as the time dimension is not being used properly.

Anybody an idea how to solve this more efficient (in 1 measure, but having the time dimension seperately ?)

For everyones information, I will try to describe my case more thorougly:

Based on opportunities in our CRM tool, I want to show the sales pipeline (amount in sales phase on a specific date) and I use following checks to calculate this from past dates:

(1) Creation date, which should be before date X

(2) End date, which should be blank or after date X

For the moment I have following (DIM_Date) table:
datetable.JPG

Which is having a relation with the CRM date (Sales_opportunities):

 

Relationship date table & sales opportunities.JPG

If I try to change the formula, from hard coded dated towards the Date field onder Date table (Dim_Date) I get following error:

 

Wrong formula Dim_Date.JPG

Many thanks for looking into this.

 

Kind regards,

 

Gerry

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors