Sum value for three month based on slicer

[notrop8]

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

[grazitti_sapna]

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

[notrop8]

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.

[grazitti_sapna]

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]

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.

[notrop8]

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.

[Ashish_Mathur]

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.

[notrop8]

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

[Ashish_Mathur · Accepted solution]

Hi,

Here's the PBI file.

Hope this helps.

[notrop8]

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

[Ashish_Mathur]

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

[notrop8]

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

[v-tangjie-msft]

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. 

[notrop8]

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]

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.