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

     

    • evest's avatar
      evest
      Regular Visitor
      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])
       
       
      )
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

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

         

         

        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.