Forum Discussion

shelbsassy's avatar
shelbsassy
Resolver I
9 years ago
Solved

Eliminating null or duplicate rows in a table calculated from summarize function

I created a table to get the distinct irst date of the month and the Max Service Date (from the data load) from  TrendCalcs = SUMMARIZE(Dates,Dates[FirstDateC],Dates[MaxPerMonth]) and I added a colu...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Hi shelbsassy

     

    You could try something like this?

     

    TrendCalcs = SUMMARIZE(
        FILTER(
            Dates,
            'Dates'[FirstDateC]<> BLANK()
            ),
            'Dates'[FirstDateC],
            Dates[MaxPerMonth]
            )
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Hi shelbsassy

 

You could try something like this?

 

TrendCalcs = SUMMARIZE(
        FILTER(
            Dates,
            'Dates'[FirstDateC]<> BLANK()
            ),
            'Dates'[FirstDateC],
            Dates[MaxPerMonth]
            )
  • shelbsassy's avatar
    shelbsassy
    Resolver I
    9 years ago

    NOt sure if you can help me with the next part of this problem but I have the calculated ciolumn called MOnthDays which is 

    MonthDays = VAR LastDayThisMonth = CALCULATE(MAX(Dates[Date]), ALLEXCEPT(Dates,Dates[YearMonth])) RETURN (DAY(LastDayThisMonth))

    to give me the number if days in a month.  Basically I want to divide that NoDays from the TrendCalcs table by the maximum number of days in that month.  Everytime I try to create a measure or a column I am either not getting the field I need in the intellisense dropdown or when I try to do a sum(dates([monthdays]/sum(trendcalcs[NoDays]) I am getting a calculation for everythign in that column but I only want the distinct value for that month.  The result is 209.64.  

     

    For January since there are 31 days in the month and the max date of data is 31 then the value should be 1 when 31/31.  For February theres 28 days and the max date of data is 22 so the value should be .7857.

     

    Any thoughts or insight?  Thank you!

  • shelbsassy's avatar
    shelbsassy
    Resolver I
    9 years ago

    OMG Worked beautifully the first try!  Thank you so much!