Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All,
I have a data table with subscriptions in it.
Start date | End date | Amount | Frequency | Total Amount | Customer |
1-1-2021 | 31-12-2021 | 100 | Month | 1200 | A |
1-12-2020 | 28-2-2021 | 500 | Month | 1500 | B |
1-3-2021 | 31-8-2021 | 1000 | Quarterly | 2000 | C |
1-4-2021 | 31-4-2021 | 3000 | Once | 3000 | D |
1-6-2021 | 31-6-2021 | 2000 | Year | 2000 | E |
1-1-2021 | 31-12-2021 | 100 | 4 Weekly | 1300 | F |
I want to create an month by month overview of the amounts as below
Month | To be invoiced |
1 | (A = 100) (B = 500) (F= 100) = 700 euro |
2 | (A = 100) (B = 500) (F= 100) = 700 euro |
3 | (A = 100) (F= 100) (C = 1000)= 1200 euro |
4 | (A = 100) (F= 100) (D = 3000)= 3200 euro |
5 | (A = 100) (F= 100)= 200 euro |
6 | (A = 100)(C=1000) (F= 100) (E = 2000) = 3200 euro |
The rules are:
Month amounts are invoiced every month beginning on [start date] ending on [end date]
Quarterly are invoiced beginning on [start date] and every 3 months after that until [end date]
Year & Once are invoiced in month of [start date]
4 weekly is invoiced from start date til end date with intervals of 4 weeks (I am willing to compromise and treat 4 weeks as month)
Hope you can help me
Solved! Go to Solution.
Hi @BobKoenen ,
Try to add the following measure:
TOTAL =
VAR Monthly_value =
SUMX (
FILTER (
CROSSJOIN (
CALCULATETABLE (
FILTER ( 'Table'; 'Table'[Frequency ] IN { "Month"; "4 Weekly" } )
);
'Calendar'
);
'Calendar'[Date]
= DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Calendar'[Date] ); DAY ( 'Table'[Start date] ) )
&& 'Calendar'[Date] >= 'Table'[Start date]
&& 'Calendar'[Date] <= 'Table'[End date]
);
'Table'[Amount ]
)
VAR Yearly_Amount =
SUMX (
FILTER (
CROSSJOIN (
CALCULATETABLE ( FILTER ( 'Table'; 'Table'[Frequency ] = "Year" ) );
'Calendar'
);
'Calendar'[Date]
= DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ); DAY ( 'Table'[Start date] ) )
&& 'Calendar'[Date] >= 'Table'[Start date]
&& 'Calendar'[Date] <= 'Table'[End date]
);
'Table'[Amount ]
)
VAR Once_Amount =
SUMX (
FILTER (
CROSSJOIN (
CALCULATETABLE ( FILTER ( 'Table'; 'Table'[Frequency ] = "Once" ) );
'Calendar'
);
'Calendar'[Date] = 'Table'[Start date]
);
'Table'[Amount ]
)
VAR Quartely_Amount =
SUMX (
FILTER (
CROSSJOIN (
CALCULATETABLE ( FILTER ( 'Table'; 'Table'[Frequency ] = "Quarterly" ) );
'Calendar'
);
'Calendar'[Date] >= 'Table'[Start date]
&& 'Calendar'[Date] <= 'Table'[End date]
&& 'Calendar'[Date]
IN {
DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ); DAY ( 'Table'[Start date] ) );
DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ) + 3; DAY ( 'Table'[Start date] ) );
DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ) + 6; DAY ( 'Table'[Start date] ) );
DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ) + 9; DAY ( 'Table'[Start date] ) );
DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ) + 12; DAY ( 'Table'[Start date] ) )
}
);
'Table'[Amount ]
)
RETURN
Monthly_value + Quartely_Amount + Once_Amount + Yearly_Amount
Be aware that if the start date of the contract is on the last day of the month you may need to adjust the formulas since some dates may not appear based on the date.
See result below:
Based on my test should work even with end dates higher than 1 year, that is why I made the quartely to only finish in 2025.
Has you have refered I have consider the 4 weeks in the month payments
PBIX attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @BobKoenen ,
Try to add the following measure:
TOTAL =
VAR Monthly_value =
SUMX (
FILTER (
CROSSJOIN (
CALCULATETABLE (
FILTER ( 'Table'; 'Table'[Frequency ] IN { "Month"; "4 Weekly" } )
);
'Calendar'
);
'Calendar'[Date]
= DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Calendar'[Date] ); DAY ( 'Table'[Start date] ) )
&& 'Calendar'[Date] >= 'Table'[Start date]
&& 'Calendar'[Date] <= 'Table'[End date]
);
'Table'[Amount ]
)
VAR Yearly_Amount =
SUMX (
FILTER (
CROSSJOIN (
CALCULATETABLE ( FILTER ( 'Table'; 'Table'[Frequency ] = "Year" ) );
'Calendar'
);
'Calendar'[Date]
= DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ); DAY ( 'Table'[Start date] ) )
&& 'Calendar'[Date] >= 'Table'[Start date]
&& 'Calendar'[Date] <= 'Table'[End date]
);
'Table'[Amount ]
)
VAR Once_Amount =
SUMX (
FILTER (
CROSSJOIN (
CALCULATETABLE ( FILTER ( 'Table'; 'Table'[Frequency ] = "Once" ) );
'Calendar'
);
'Calendar'[Date] = 'Table'[Start date]
);
'Table'[Amount ]
)
VAR Quartely_Amount =
SUMX (
FILTER (
CROSSJOIN (
CALCULATETABLE ( FILTER ( 'Table'; 'Table'[Frequency ] = "Quarterly" ) );
'Calendar'
);
'Calendar'[Date] >= 'Table'[Start date]
&& 'Calendar'[Date] <= 'Table'[End date]
&& 'Calendar'[Date]
IN {
DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ); DAY ( 'Table'[Start date] ) );
DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ) + 3; DAY ( 'Table'[Start date] ) );
DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ) + 6; DAY ( 'Table'[Start date] ) );
DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ) + 9; DAY ( 'Table'[Start date] ) );
DATE ( YEAR ( 'Calendar'[Date] ); MONTH ( 'Table'[Start date] ) + 12; DAY ( 'Table'[Start date] ) )
}
);
'Table'[Amount ]
)
RETURN
Monthly_value + Quartely_Amount + Once_Amount + Yearly_Amount
Be aware that if the start date of the contract is on the last day of the month you may need to adjust the formulas since some dates may not appear based on the date.
See result below:
Based on my test should work even with end dates higher than 1 year, that is why I made the quartely to only finish in 2025.
Has you have refered I have consider the 4 weeks in the month payments
PBIX attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThankx Alot. This is awesome and works really good. !
Assuming that you have a calendar table.
Use GENERATE() or GENERATESERIES() to create temporary tables for each of the subscriptions and their rules, and then add the result up for the respective months.
For your "4 weeks" logic you will also need to specify what you mean by week (start day, duration if different from 7 days etc)
You have an error in your sample for subscription E. End date should be 2022-05-31 or later. Similar, there is no April 31 for D.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |