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.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

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