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

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!