Forum Discussion

Allisond's avatar
Allisond
Icon for Advocate II rankAdvocate II
5 years ago
Solved

Summing a Daily Measure by Month or Year

I have a measure that works perfectly for census if I have it in a matrix or table and filter by dates, however, when I try to group by month I am getting errors.   Can anyone help me with out to get this to group by total month without having to put in a matrix and us the drill down?
 
I would like a measure that just gives me the total census for a given month that still respondes to location filters that I can use as a denominator for other calculations. 
 
Census =
VAR StartDate = VALUE(SELECTEDVALUE( 'Billing Census Detail'[StartDate] ) )
VAR BillingEndDate = VALUE(SELECTEDVALUE( 'Billing Census Detail'[Billing End Date] ) )
VAR MinDate = VALUE( MIN( Dates[DayDate] ) )
VAR MaxDate = VALUE( MAX( Dates[DayDate] ) )

RETURN
IF( AND( StartDate <= MinDate, BillingEndDate >= MinDate ) ,
MIN(BillingEndDate, MaxDate ) - MinDate +1,
IF( AND( AND( StartDate >= MinDate, StartDate <= MaxDate), BillingEndDate >= MinDate),
MIN( BillingEndDate, MaxDate) - StartDate +1,
BLANK()) )
 
Thank you for any help you can provide.  It is making me crazy and I am sure it is something simple I am missing. 
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Allisond 

    Do you mean cumulative total of the daily values by month? Try something like:

     

    Measure = calculate(sum([value]), filter(allselected(date),[date].[month.no]=max([date].[month.no]) && [date]<=max([date]))


    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

5 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    5 years ago

    Allisond 

    Try adding a new measure that uses SUMX and see if that works.

    Census SUM = SUMX( Dates[DayDate], [Census] )
    • Allisond's avatar
      Allisond
      Icon for Advocate II rankAdvocate II
      5 years ago

      Won't let me set it up.  SUMX is requiring a table, so I added the table then column, but in adding the Census Measure just errors altogether.

      • Allisond's avatar
        Allisond
        Icon for Advocate II rankAdvocate II
        5 years ago

        I can use SUMX(Values('Table'[Census])  which does give me a daily value filterable by unique center, date, etc. but I can't figure out how to add the daily values to get a monthly total in a measure.