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
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

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!