Using DAX or a measure on a calendar table toshow the sum of values when the date is within a range

[Anonymous]

Hi,

I'm fairly new to PowerPivot and PowerQuery. I have used this forum quite a lot to figure out some basics (thanks for that!). However, for one of my data models I have an issue for which I cannot figure out a solution.

Short introduction

I am aiming to create a table which shows both the revenue and the sales bonus. The revenue is obtained as a data model table from a certain system whereas the sales bonus will be calculated based on the revenue (either in € or in KG). For all different types of bonus measures in place I have calculated the bonus, this works great by using (simple) merge functions and calculate functions. 

However, there are also two tables which show a fixed monthly bonus. For these tables it would be more complex to merge them with the sales data, first of all I would need more complex calculations but most of all, when a client doesn't have a sale the fixed bonus will be zero which is unwanted as the fixed bonus should always be applied. 

I therefore though of creating either a separate table or PivotTable (using DAX or a measure). By taking a calendar table and the bonus table I would be able to show the total monthly bonus per month. However, there is also the fact that the bonus table shows diffent bonusses for certain ranges. 

Data tables

Tbl_DebVastBedrag

StartDate	EndDate	DebNum	DebNaam	VastBedrag
1-10-2019	31-12-2019	50401	FAKENAME	€                 1.333,33

Calendar table:

[Date], [Month], [Month MMM-YYYY], [Period], [Year], [Month Number]

There are no relations between the two tables (yet). 

Goal

Create either a new table (using DAX) or a PivotTable (using a measure) which shows the following:

Month MMM-YYYY	VastBedrag
2019-10	The sum of all [VastBedrag] for which [Month MMM-YYYY] is within the range [StartDate] and [EndDate]

Afterwards I would create a new Pivot which shows the allready created bonus calculations and combine them with the fixed bonus.

Many thanks in advance,

Tim

[Jimmy801]

Hello @Anonymous 

have you been able to solve the problem with the replies given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[Anonymous]

Hi @Jimmy801,

Sorry, got caught up and forgot about it!

Thanks for your help.

Tim

[Jimmy801 · Accepted solution]

Hello @Anonymous 

if you need to do this in DAX you can do as follows:

Create new table by using this syntax. Creates a Columns with the number of months involved in your main table

Month = GENERATESERIES(1;DATEDIFF(min(Tbl_DebVastBedrag[StartDate]);max(Tbl_DebVastBedrag[EndDate]);MONTH)+1)

Add a new colum calles StartOfMonth with this Syntax

StartOfMonth = Date(year(STARTOFMONTH(Tbl_DebVastBedrag[StartDate]));MONTH(min(Tbl_DebVastBedrag[StartDate]))+[Value]-1;1)

Add another column to do the final calculation

Bonus = CALCULATE(
   sum(
      Tbl_DebVastBedrag[VastBedrag]);
      filter(
         Tbl_DebVastBedrag;
         Tbl_DebVastBedrag[EndDate]>=earlier('Month'[StartOfMonth])&&Tbl_DebVastBedrag[StartDate]<=EOMONTH(EARLIER('Month'[StartOfMonth]);0)
      )
   )

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy