cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yiying
Helper I
Helper I

Create Measure that shows monthly total under each day

Capture.PNGHere is what my data look like now. I have been trying to create a new measure that for each day in December, it will show the same number 122251, which is the total. The number changes by month of course. Thanks!

5 REPLIES 5
Zubair_Muhammad
Community Champion
Community Champion

Hi @yiying

 

Try this MEASURE

 

Measure =
CALCULATE (
    [CountMeasure],
    FILTER (
        ALL ( TableName ),
        MONTH ( TableName[Dates] ) = MONTH ( SELECTEDVALUE ( TableName[Dates] ) )
            && YEAR ( TableName[Dates] ) = YEAR ( SELECTEDVALUE ( TableName[Dates] ) )
    )
)
Regards
Zubair

Please try my custom visuals


Thank you so much it works!!

 

Another question. I also have a attribute "Type". When I add the filter , the new measure I created does not change. What should I do? 

HI @yiying

 

Try Replacing ALL with ALLEXCEPT....

 

Check this revised code

 

Measure =
CALCULATE (
    [CountMeasure],
    FILTER (
        ALLEXCEPT ( TableName, TableName[Attribute] ),
        MONTH ( TableName[Dates] ) = MONTH ( SELECTEDVALUE ( TableName[Dates] ) )
            && YEAR ( TableName[Dates] ) = YEAR ( SELECTEDVALUE ( TableName[Dates] ) )
    )
)
Regards
Zubair

Please try my custom visuals


@yiying

 

Alternatively this should work as well

 

Just replace ALL(TableName) with All(TableName[Dates])

 

Measure =
CALCULATE (
    [CountMeasure],
    FILTER (
        ALL ( TableName[Dates] ),
        MONTH ( TableName[Dates] ) = MONTH ( SELECTEDVALUE ( TableName[Dates] ) )
            && YEAR ( TableName[Dates] ) = YEAR ( SELECTEDVALUE ( TableName[Dates] ) )
    )
)
Regards
Zubair

Please try my custom visuals


Thank you! The allexcept works. But the on with all does not work for some reason. 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors