Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Get total per month

Hi everyone,
I'm trying to create a chart(but first im using a table visual to see my measures) that shows per month the value of my measure.

Postigo_1-1626371869683.png

So far i got this, the problem is when i use my slicerto select for example 15/03/2021 that will filter all the MonthYear of 202103, and give me this 3 lines 

Postigo_3-1626372170069.png

total Valueleft:1.117.503

what i want is that the measure "Measure" be the sum of the "Valueleft" and when i select for example 08/03/2021 only show me this 2 lines(and the sum of only that 2 lines).

Postigo_4-1626372278921.png
total Valueleft:1.067.503

PowerBi File 

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ;

You could create measure as follows:

Measure =
SUMX (
    FILTER (
        ALL ( Planilha1 ),
        [DatePayment] <= MAX ( 'DCalendar'[Dates] )
            && EOMONTH ( [DatePayment], 0 ) = EOMONTH ( MAX ( 'DCalendar'[Dates] ), 0 )
    ),
    [ValueLeft])

and then create a flag measure to show only the rows that meet the requirements in Visuals .

flag =
IF (
    MAX ( [DatePayment] ) <= MAX ( 'DCalendar'[Dates] )
        && EOMONTH ( MAX ( [DatePayment] ), 0 ) = EOMONTH ( MAX ( 'DCalendar'[Dates] ), 0 ),
    1)

and apply it into filter.

vyalanwumsft_1-1626685917766.png

The final output is shown below:

vyalanwumsft_2-1626685975350.pngvyalanwumsft_3-1626685989515.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous ;

You could create measure as follows:

Measure =
SUMX (
    FILTER (
        ALL ( Planilha1 ),
        [DatePayment] <= MAX ( 'DCalendar'[Dates] )
            && EOMONTH ( [DatePayment], 0 ) = EOMONTH ( MAX ( 'DCalendar'[Dates] ), 0 )
    ),
    [ValueLeft])

and then create a flag measure to show only the rows that meet the requirements in Visuals .

flag =
IF (
    MAX ( [DatePayment] ) <= MAX ( 'DCalendar'[Dates] )
        && EOMONTH ( MAX ( [DatePayment] ), 0 ) = EOMONTH ( MAX ( 'DCalendar'[Dates] ), 0 ),
    1)

and apply it into filter.

vyalanwumsft_1-1626685917766.png

The final output is shown below:

vyalanwumsft_2-1626685975350.pngvyalanwumsft_3-1626685989515.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

first of all, make sure your DatePayment is in DATE format, not in TEXT format.

if it is in date format, you can use following slicer to filter out data]

yfeng_0-1626376309835.png

 

Anonymous
Not applicable

Unfortunately this doesn't work,cause the measure ValueLeft gets the wrong value

Postigo_0-1626438920952.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.