Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
evest
Regular Visitor

Getting Totals by day with dynamic dates

‎12-15-2021 10:48 PM

@ links to members, content

I am trying to create a calculation in my table/matrix but cannot get it to work.  I need to be able to do the following: 

1. # of accounts per office that do not have a monthly premium or monthly revenue based on the effective date and this needs to be by day.

2. The date will have to be dynamic as new data comes in the dates will change.

3. I need a filter by year, month and quarter

 

Here is my data.  I have also attached the pbix file 

 OfficeYearQuarterMonthDay#AccountsFirst Monthly RevenueMonthly Premium
a2021Qtr 1January1623025471103.87
a2021Qtr 1January21635117682.38
a2021Qtr 1February110101.33451420.96
a2021Qtr 1March125106.26214084.0644
a2021Qtr 2April1210597595.9249
a2021Qtr 2April152142.661518.24
a2021Qtr 2May1400186171.6251
a2021Qtr 2June1250602502.395
a2021Qtr 3July1500703467.6209
a2021Qtr 3August1390424631.915
a2021Qtr 3September1480800442.3436
a2021Qtr 4October118106.699250470.5051
a2021Qtr 4November119154.98377939.8981
a2021Qtr 4December129103.1238160952.7321
b2021Qtr 1January11040320159.71
b2021Qtr 1January15410.199483236.57
b2021Qtr 1February118030137.69
b2021Qtr 1March110121.6421878.68
b2021Qtr 1March1514.8332.2
b2021Qtr 2April124152152.67
b2021Qtr 2April1512.7655.27
b2021Qtr 2May13110.6755156967.902
b2021Qtr 2May151265.90955318.19
b2021Qtr 2June1240183073.72
b2021Qtr 2June154167.8076943.24
b2021Qtr 3July125100.08144132.84
b2021Qtr 3July15129.988705.6
b2021Qtr 3July2515.19351.93
b2021Qtr 3August12210.62549404.38
b2021Qtr 3September118106.16730897.91
b2021Qtr 4October1430176298.83
b2021Qtr 4October152252.100236018.12
b2021Qtr 4November17182.4554813131.95
b2021Qtr 4November1523.731215.56
b2021Qtr 4December118107.6368560358.72
c2021Qtr 1January11286026637748.19
c2021Qtr 1January2126.427264.27
c2021Qtr 1January31132.84885.6
c2021Qtr 1January201  
c2021Qtr 1February1890855855.42
c2021Qtr 1February112 288.5
c2021Qtr 1February2010336.87
c2021Qtr 1March1500214722.26
c2021Qtr 1March214000
c2021Qtr 1March261Net40987.02
c2021Qtr 2April110301691212.986
c2021Qtr 2April2223.34242762.67
c2021Qtr 2April3100
c2021Qtr 2April151 0
c2021Qtr 2April301  
c2021Qtr 2May1830581586.9402
c2021Qtr 2June11580884514.284
c2021Qtr 2June81 0
c2021Qtr 2June912.830830
c2021Qtr 2June151 0
c2021Qtr 3July11050797416.5322
c2021Qtr 3July2171.41428
c2021Qtr 3August1550754956.2737
c2021Qtr 3September1860669460.0746
c2021Qtr 3September281358.7514369
c2021Qtr 4October110604006931.365
c2021Qtr 4October12100
c2021Qtr 4October311 0
c2021Qtr 4November1670935427.1776
c2021Qtr 4December116601692949.315
c2021Qtr 1January1901572.1
c2021Qtr 2April1100
c2021Qtr 2May1218.06667130.09
c2021Qtr 2June11 0
c2021Qtr 3July1100
c2021Qtr 3September1100
d2021Qtr 1January125051118.58
d2021Qtr 1March113046302.23672
d2021Qtr 2April115098940.9
d2021Qtr 2June16104994.91
d2021Qtr 3July122043445.6175
d2021Qtr 3August1120.28822674.77
d2021Qtr 3September1890496045.3088
d2021Qtr 4October111017341.8354
d2021Qtr 4November119018841.76
d2021Qtr 4December11209609.3513
Labels:
Message 1 of 12
1,163 Views
0
3 ACCEPTED SOLUTIONS
charleshale
Continued Contributor
Continued Contributor

‎12-29-2021 06:03 PM

I'm having trouble understanding the tie between the answer key and the data but would something like this help provided you have a source column for when the accounts activate and leave??   I'm assuming you have date table. ****[see sample at bottom called dimdate]

 

 

 

Accounts Active= 
VAR _startofperiod = MIN ( DimDate[Date] )
VAR _endofperiod = MAX ( DimDate[Date] ) 
VAR _threshold = sum(//revenue column goes here//) 
RETURN 
CALCULATE ( sum( Table1[#Accounts] ), 
Table1[//DateAdded//] <= _endofperiod, 
Table1[//revenue//]>=_thresholddate, 
Table1[//DateInactive//] > _endofperiod) 
), 
REMOVEFILTERS ( DimDate )
 ) ​

 

 
****If not, try this code for a default date table.   Just click Table Tools / New Table and paste in 

 

 

 

DimDate = 
VAR MINYEAR = 2008  //or some other min year
VAR MAXYEAR = 2021
VAR _DateTable = 
ADDCOLUMNS (
    FILTER (
        CALENDARAUTO( ),
        AND ( YEAR ( [DATE] ) >= MINYEAR, YEAR ( [DATE] ) <= MAXYEAR )
    ),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYYMM" ),
"YearMonthtext", FORMAT ( [Date], "YYYYMM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "QTR-" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
"YearQuarterNumber", Year([Date])*4 + QUARTER([Date]),
"HalfYear", YEAR([Date])&" H"&ROUNDUP(MONTH([Date])/6,0),
"LastImport", max(Table2[Max Date])
)
RETURN
_DateTable

 

 

 

 

 

 

 

 

 

 

 

View solution in original post

Message 10 of 12
1,040 Views
0
Anonymous
Not applicable
In response to evest

‎12-29-2021 08:13 PM

Hi @evest ,

It's hard to achieve the look you want in the matrix. I have tried another way, please  see if it helps.

Create measures.

Year_2021 = CALCULATE(SUM('Table'[First Monthly Revenue]),FILTER(ALL('Table'),'Table'[date].[Year]=SELECTEDVALUE('Table'[date].[Year])))
month_1 = CALCULATE(SUM('Table'[First Monthly Revenue]),FILTER(ALL('Table'),'Table'[date].[MonthNo]=SELECTEDVALUE('Table'[date].[MonthNo])))

11.PNG

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Getting Totals by day with dynamic dates.pbix
Message 7 of 12
1,030 Views
0
evest
Regular Visitor
In response to Anonymous

‎12-29-2021 09:49 PM

This works but now it wont show my previous months value.  Here is my dax 

Prev Month Premium =
CALCULATE('Jan/Feb2022(Active_Plans___Estimated_Premiu)'[TTL Prem2022],PREVIOUSMONTH('Jan/Feb2022(Active_Plans___Estimated_Premiu)'[Effective Date])
 
 
)

View solution in original post

Message 8 of 12
1,019 Views
0
11 REPLIES 11
charleshale
Continued Contributor
Continued Contributor

‎12-29-2021 06:03 PM

I'm having trouble understanding the tie between the answer key and the data but would something like this help provided you have a source column for when the accounts activate and leave??   I'm assuming you have date table. ****[see sample at bottom called dimdate]

 

 

 

Accounts Active= 
VAR _startofperiod = MIN ( DimDate[Date] )
VAR _endofperiod = MAX ( DimDate[Date] ) 
VAR _threshold = sum(//revenue column goes here//) 
RETURN 
CALCULATE ( sum( Table1[#Accounts] ), 
Table1[//DateAdded//] <= _endofperiod, 
Table1[//revenue//]>=_thresholddate, 
Table1[//DateInactive//] > _endofperiod) 
), 
REMOVEFILTERS ( DimDate )
 ) ​

 

 
****If not, try this code for a default date table.   Just click Table Tools / New Table and paste in 

 

 

 

DimDate = 
VAR MINYEAR = 2008  //or some other min year
VAR MAXYEAR = 2021
VAR _DateTable = 
ADDCOLUMNS (
    FILTER (
        CALENDARAUTO( ),
        AND ( YEAR ( [DATE] ) >= MINYEAR, YEAR ( [DATE] ) <= MAXYEAR )
    ),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYYMM" ),
"YearMonthtext", FORMAT ( [Date], "YYYYMM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "QTR-" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
"YearQuarterNumber", Year([Date])*4 + QUARTER([Date]),
"HalfYear", YEAR([Date])&" H"&ROUNDUP(MONTH([Date])/6,0),
"LastImport", max(Table2[Max Date])
)
RETURN
_DateTable

 

 

 

 

 

 

 

 

 

 

 

Message 10 of 12
1,041 Views
0
evest
Regular Visitor
In response to charleshale

‎01-03-2022 08:53 PM

@charleshale 

 

Charles thanks for the calendar table.  It helped a lot. But now I am having trouble getting the previous month percentages to show.  This is what the matrix should display in red.  There is no prior month to December, therefore it will be blank but January should show December values.  Any help would be appreciated.  Here is my dax formula 

I am dividing the number of plans by commission amount

Prior Reporting Period % of Plans w/Est Comm =
Var EndDate = MAX('Jan 2022'[Report Date])
VAR StartDate = EDATE(EndDate, -1)
Var Result =
Calculate(Sum('Jan 2022'[Commission Amount]),
DATESBETWEEN('Jan 2022'[Report Date],StartDate,EndDate)
)
Return
Result

 

Office#PlansCommissionReport DateCurrent PercentPrevious Percent
ABC15768543Dec1.95%0.0%
ABC885432Jan9.36%1.95%
DEF40514654Dec.027% 
DEF433194Jan23%.027%
Message 11 of 12
950 Views
0
charleshale
Continued Contributor
Continued Contributor
In response to evest

‎01-04-2022 06:56 PM

Aha - I'd recommend connecting the date table to the date you are using in the table of commission calcs and then using either previousmonth() or this

 

So -- for current period

Prior Reporting Period % of Plans w/Est Comm =
VAR _EndDate = Max(dimdate[date])
VAR _StartDate = Min(dimdate[date])
Var _Result =
Calculate(Sum('Table'[Commission]),
 Dimdate[Date]>= _startdate,
 Dimdate[Date]<= _Enddate)
Return
_Result
 
For previous month 
Previous Month Commission = calculate ( [Prior Reporting Period % of Plans w/Est Comm], previousmonth(dimdate[date])

 

 

 

Message 12 of 12
918 Views
0
Anonymous
Not applicable

‎12-19-2021 10:58 PM

Hi @evest ,

Unfortunately, there's no way to automatically create measures in Power BI.

You can submit an idea for it at https://ideas.powerbi.com/forums and wait for users with the same needs as you to vote for you to help make it happen as soon as possible.

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Message 5 of 12
1,076 Views
0
evest
Regular Visitor
In response to Anonymous

‎12-28-2021 11:01 PM

I did not get a solution from this forum.  So I will rephrase my question.  What I need to show in the matrix is pre aggregation for the year 2021 and monthly totals for 2022.   This is simple to do in other reporting tools why is it so hard to do in Power BI

 

Total for entire 20212022  
 JanuaryFeburary
123,690150678
Message 6 of 12
1,047 Views
0
Anonymous
Not applicable
In response to evest

‎12-29-2021 08:13 PM

Hi @evest ,

It's hard to achieve the look you want in the matrix. I have tried another way, please  see if it helps.

Create measures.

Year_2021 = CALCULATE(SUM('Table'[First Monthly Revenue]),FILTER(ALL('Table'),'Table'[date].[Year]=SELECTEDVALUE('Table'[date].[Year])))
month_1 = CALCULATE(SUM('Table'[First Monthly Revenue]),FILTER(ALL('Table'),'Table'[date].[MonthNo]=SELECTEDVALUE('Table'[date].[MonthNo])))

11.PNG

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Getting Totals by day with dynamic dates.pbix
Message 7 of 12
1,031 Views
0
evest
Regular Visitor
In response to Anonymous

‎12-29-2021 09:49 PM

This works but now it wont show my previous months value.  Here is my dax 

Prev Month Premium =
CALCULATE('Jan/Feb2022(Active_Plans___Estimated_Premiu)'[TTL Prem2022],PREVIOUSMONTH('Jan/Feb2022(Active_Plans___Estimated_Premiu)'[Effective Date])
 
 
)
Message 8 of 12
1,020 Views
0
Anonymous
Not applicable
In response to evest

‎12-29-2021 10:26 PM

Hi @evest ,

Please have a try.

Create a column firstly.

 

Month_c = TOTALMTD(MAX('Table'[date].[MonthNo]),'Table'[date],FILTER(ALL('Table'),[Date].[MonthNo]=EARLIER('Table'[date].[MonthNo])))

 

Then create a measure.

 

sum_ = CALCULATE(SUM('Table'[First Monthly Revenue]),FILTER(ALL('Table'),'Table'[Month_c]<=SELECTEDVALUE('Table'[Month_c])&&'Table'[date].[Year]=SELECTEDVALUE('Table'[date].[Year])))

11.PNG

 

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Getting Totals by day with dynamic dates.pbix
Message 9 of 12
1,015 Views
0
charleshale
Continued Contributor
Continued Contributor

‎12-16-2021 08:00 PM

Can you share an example of what (1) would look like?

Message 2 of 12
1,106 Views
0
evest
Regular Visitor
In response to charleshale

‎12-28-2021 11:00 PM

I did not get a solution from this forum.  So I will rephrase my question.  What I need to show in the matrix is pre aggregation for the year 2021 and monthly totals for 2022.  

 

Total for entire 20212022  
 JanuaryFeburary
123,690150678
Message 4 of 12
1,047 Views
0
evest
Regular Visitor
In response to charleshale

‎12-16-2021 10:16 PM

This is what it should look like.  The totals will include each day of the month where the day is missing monthly revenue and monthly premium in these columns the value is 0 or null. This is for 2021 but when I run the report the dates should adjust to 2022 dates.  The dates you are looking at are the dates the report is run for that month.

OfficeCount as of 1/11/2021Count as of 2/25/2021Count as of 3/8/2021Count as of4/27/2021Count as of 5/10/2021Count as of6/23/2021Count as of7/12/2021Count as of 8/19/2021Count as of 9/15/2021Count as of 11/18/2021Count as of 12/14/2021
a3046352223202232272738
b66075811023
c2641341131081138341375176122
d       52525153
e3734313830393936904344
f3119192021232221212425
g20180669112621
h1414111317211214112025
i3030292425252829363536
j862184541131
k6658597478626174746391
l01000003333
m29593759716457697773153
n55577777777
o47682471116142211
p9351283121012351278126611591206121812101260
Message 3 of 12
1,092 Views
0

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All