Forum Discussion

evest's avatar
evest
Regular Visitor
4 years ago
Solved

Getting Totals by day with dynamic dates

@ 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
Show and Tell
Tips and Tricks
  • charleshale's avatar
    charleshale
    4 years ago

    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

     

     

     

     

     

     

     

     

     

     

     

  • Anonymous's avatar
    Anonymous
    4 years ago

    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])))

    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.

     

  • evest's avatar
    evest
    4 years ago

    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])
     
     
    )

11 Replies

    • evest's avatar
      evest
      Regular Visitor
      4 years ago

      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
    • evest's avatar
      evest
      Regular Visitor
      4 years ago

      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
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

    • evest's avatar
      evest
      Regular Visitor
      4 years ago

      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
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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])))

        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.

         

  • charleshale's avatar
    charleshale
    Icon for Continued Contributor rankContinued Contributor
    4 years ago

    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

     

     

     

     

     

     

     

     

     

     

     

    • evest's avatar
      evest
      Regular Visitor
      4 years ago

      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%
      • charleshale's avatar
        charleshale
        Icon for Continued Contributor rankContinued Contributor
        4 years ago

        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])