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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
notrop8
Frequent Visitor

Sum value for three month based on slicer

Dear Experts,

 

I have a sales table with 'project_name', 'country', 'value', 'start_date', and 'due_date' (notably). 

I have several slicers, such as 'calendar(date)' and 'country'.

 

I want to create a measurement for a bar chart , that will, when I select one month on the slicer, sum up all 'value' for the selected month, until two month after (so M to M+2). So if 'January' 2021 is selected, I want the sum of all 'value' for rows being between '01-01-2021' and '03-01-2021'.

 

I have come up with this measurement :

 

 

 

Measure M to M+2 Value = 
VAR selected_date = MIN('Calendar'[Date])
RETURN 
CALCULATE(
    SUM('sales'[value]),
            FILTER('sales',
            'sales'[due_date] >= selected_date && 'sales'[due_date] <= EOMONTH(selected_date , 2)))

 

 

 

But I figured out that apparently if two (or more) rows have the same country and due_date, it will only select the first one, wheras I want the sum of all (because they are different project_name)

 

How can I achieve this ?

Thanks a lot

1 ACCEPTED SOLUTION

Hi,

Here's the PBI file.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

14 REPLIES 14
grazitti_sapna
Resolver I
Resolver I

Hey, you can try using 

Measure M to M+2 Value =

VAR selected_date = MIN('Calendar'[Date])

VAR endDate = EOMONTH(selected_date, 2)

RETURN

CALCULATE(

SUM('sales'[value]),

FILTER(

'sales',

'sales'[due_date] >= selected_date &&

'sales'[due_date] <= endDate &&

'sales'[country] IN VALUES('sales'[country]) &&

'sales'[project_name] IN VALUES('sales'[project_name])

)

)

Thankyou

Hi and thank you.

 

The calculation done is good but it is only summing the selected month, and not the selected month and the two following. (If I select only January, it sums only 'value' for january wheras I want sum of 'value' for january, february and march.

 

How can I fix this ? Thanks. 

 

If it helps, you can find here or here or here the example files.

Hey,

Try Using 
Measure M to M+2 Value =
VAR selected_date = MIN('Calendar'[Date])
VAR endDate = EOMONTH(selected_date, 2)
VAR selected_month = MONTH(selected_date)
VAR selected_year = YEAR(selected_date)
RETURN
CALCULATE(
    SUM('sales'[value]),
    FILTER(
        'sales',
        YEAR('sales'[due_date]) * 100 + MONTH('sales'[due_date]) >= selected_year * 100 + selected_month &&
        YEAR('sales'[due_date]) * 100 + MONTH('sales'[due_date]) <= selected_year * 100 + selected_month + 2 &&
        'sales'[country] IN VALUES('sales'[country]) &&
        'sales'[project_name] IN VALUES('sales'[project_name])
    )
)
I hope this solves the issue for you!
Ashish_Mathur
Super User
Super User

Hi,

Create a Date slicer from the Calendar Table and select a date there.  Write these measures

Total = sum(Data[Values])

Total of upto 2 months = calculate([Total],datesbetween('Calendar'[Date],min('calendar'[Date]),edate(min('calendar'[Date]),2)))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi and thank you. The sum method is correct, however, the selected date from the slicer should be the input to filter on the 'due_date' column, to sum the 'value' for the rows with 'due_date' between selected date and two more month.

 

Please find here or here or here the example files.

You are welcome.  Just create a relationship (Many to One and Single) from the Due Date column to the Date column of the Calendar Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Sorry, but it is still not working. The result is not as expected, and the calculation is apparently not correctly done. Have you taken a look at the example pxib ?

Many thanks

Hi,

Here's the PBI file.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks, it seems to work now as I reworked the relationships.

 

I have an additonnal question please. I've added an aditionnal column 'risk' being 'low' or 'high'. I want to do the same as before, but instead of the month and two following one, I want to sum all 'value' for the selected quarter (or quarter of the selected month. e.g. quarter one if january is selected), having 'risk' equal to 'low'. How can I do this measurement ?

 

Many thanks

You are welcome.  If my previous reply helped, please mark that reply as Answer.  I cannot understand your new question.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
notrop8
Frequent Visitor

It is not fixing, with "SELECTEDVALUES" nothing is selected.

 

Here is a simmplified pbix file, with the xlsx data source for 'Sales' table : download link (I can not attach file on the forum)

As you can see, only the first occurence is selected (for Canada, only the first row for 1st January 2014 is selected, wheras I want all rows).

 

Many Thanks

Hi @notrop8 ,

 

This link shows "The transfer you requested has been deleted".Please provide a valid pbix file.

 

You can place the 'Calendar'[Date] field on the slicer and select it, then create a separate measure Measure=SELECTEDVALUE('Calendar'[Date]) and see his result, if he is invalid please try

 

Measure M to M+2 Value = 
VAR selected_date = Min('Calendar'[Date])
RETURN 
CALCULATE(
    SUM('sales'[value]),
            FILTER(ALL('sales'),
           'sales'[country]=MAX('sales'[country])&&   'sales'[due_date] >= selected_date && 'sales'[due_date] <= EOMONTH(selected_date , 2)))

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

 

Thank you for your assistance @v-tangjie-msft , but I get this error 'The MAX function only accepts a column reference as an argument.'. And yes, Measure=SELECTEDVALUE('Calendar'[Date]) returns nothing.

 

Please find here or here or here the example files.

v-tangjie-msft
Community Support
Community Support

Hi @notrop8 ,

 

Please try this measure.

 

Measure M to M+2 Value = 
VAR selected_date = SELECTEDVALUE('Calendar'[Date])
RETURN 
CALCULATE(
    SUM('sales'[value]),
            FILTER(ALL('sales'),
           'sales'[country]=MAX('sales'[country])&& 'sales'[due_date]=MAX('sales'[due_date]) &&  'sales'[due_date] >= selected_date && 'sales'[due_date] <= EOMONTH(selected_date , 2)))

 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors