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

Sum Value at the end of the month

Hi,

I need your help to solve my problem

I have a table containing dates, values and status.

The goal is to have the trend by month. The sum must be paid at the end of each month. That is all the values from the beginning until the end of the month.

I need to sum all the value that have status "FALSE" before the end of the month and the value that have status "TRUE" after the same date.

 

For example:

date                   Value       Status

22/01/2019       100           False

25/01/2019       40             False

15/02/2019       60             False

24/02/2019       80            True

 

i would like to have the result: 

31/01/2019    220

(220 = 100+40+80)

28/02/2019   200

(200= 100+40+60)

I sum 80 in January because he is true after 31/01/2019, and i don't sum it in February because in 28/02/2019 it is TRUE.

 

I hope to be able to explain myself despite my English

 

Thanks

 

Giovanni

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Sum Value at the end of the month

hi, @nannimora 

You could try this way as below:

Step1:

Add a date table and add an end of month column in it

Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))
end of the month = ENDOFMONTH('Date'[Date])

Step2:

Create a relationship between date table and data table by date column.

Step3:

Create a measure like this:

Measure =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        OR (
            'Table'[Date] <= MAX ( 'Date'[end of the month] )
                && 'Table'[Status] = FALSE (),
            'Table'[Date] > MAX ( 'Date'[end of the month] )
                && 'Table'[Status] = TRUE ()
        )
    )
)

Result:

3.JPG

 

here is pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
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

Community Support
Community Support

Re: Sum Value at the end of the month

hi, @nannimora 

Yes, it is because of the Filter ALL.

You could try to use ALLSELECTED instead of ALL

https://docs.microsoft.com/en-us/dax/all-function-dax

https://docs.microsoft.com/en-us/dax/allselected-function-dax

 

If you still have problem, just please share a simple sample pbix file and your expected out.

 

Best Regards,

Lin

Community Support Team _ Lin
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

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: Sum Value at the end of the month

hi, @nannimora 

You could try this way as below:

Step1:

Add a date table and add an end of month column in it

Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))
end of the month = ENDOFMONTH('Date'[Date])

Step2:

Create a relationship between date table and data table by date column.

Step3:

Create a measure like this:

Measure =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        OR (
            'Table'[Date] <= MAX ( 'Date'[end of the month] )
                && 'Table'[Status] = FALSE (),
            'Table'[Date] > MAX ( 'Date'[end of the month] )
                && 'Table'[Status] = TRUE ()
        )
    )
)

Result:

3.JPG

 

here is pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
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

nannimora Helper I
Helper I

Re: Sum Value at the end of the month

Perfect and Thank You so much.

I modified your measure because I have to add the "false" values on one column [Value 1] and the True values on another [Value 2].
I just don't understand why the table if I wanted to filter it by customer or sales agent doesn't change. maybe for the Filter ALL?
 
Here's how I modified it:
 
Trend = 
CALCULATE (
    SUM ( 'Table' [Value 1]  );
    FILTER (
        ALL ( 'Table' ); 
            AND('Table' [Data] <= MAX ( 'Time'[Fine Mese] );'Table' [Status] = FALSE ()
            )
    )
)
+
CALCULATE (
    SUM ( 'Table' [Value 2]  );
    FILTER (
        ALL ( 'Table' ); 
            AND('Table' [Data] > MAX ( 'Time'[Fine Mese] ); 'Table' [Status]  = TRUE ()
            )
        
    )
)
Community Support
Community Support

Re: Sum Value at the end of the month

hi, @nannimora 

Yes, it is because of the Filter ALL.

You could try to use ALLSELECTED instead of ALL

https://docs.microsoft.com/en-us/dax/all-function-dax

https://docs.microsoft.com/en-us/dax/allselected-function-dax

 

If you still have problem, just please share a simple sample pbix file and your expected out.

 

Best Regards,

Lin

Community Support Team _ Lin
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

nannimora Helper I
Helper I

Re: Sum Value at the end of the month

Thank you so much @v-lili6-msft ,

 

now, with ALLSELECTED, it should be perfect. 

 

Smiley Happy

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors