sum before a day

[Aliafshari]

Hi everyone!

 Assume that I have selected Year, Month and Day in slicer in below tale, I would like to sum the Quantity before the selected time dimension, How could I do that?

Date                      year            month           day              Quantity

1/1/2009	2009	1	1	79
1/2/2009	2009	1	2	29
1/3/2009	2009	1	3	91
1/4/2009	2009	1	4	78
1/5/2009	2009	1	5	10
1/6/2009	2009	1	6	77
1/7/2009	2009	1	7	67
1/8/2009	2009	1	8	22
1/9/2009	2009	1	9	91
1/10/2009	2009	1	10	67
1/11/2009	2009	1	11	96
1/12/2009	2009	1	12	29
1/13/2009	2009	1	13	97
1/14/2009	2009	1	14	99
1/15/2009	2009	1	15	54
1/16/2009	2009	1	16	91
1/17/2009	2009	1	17	76
1/18/2009	2009	1	18	90
1/19/2009	2009	1	19	63
1/20/2009	2009	1	20	58
1/21/2009	2009	1	21	16
1/22/2009	2009	1	22	20
1/23/2009	2009	1	23	19
1/24/2009	2009	1	24	22
1/25/2009	2009	1	25	13
1/26/2009	2009	1	26	94
1/27/2009	2009	1	27	41
1/28/2009	2009	1	28	90
1/29/2009	2009	1	29	57
1/30/2009	2009	1	30	18
1/31/2009	2009	1	31	36
2/1/2009	2009	2	1	50
2/2/2009	2009	2	2	80
2/3/2009	2009	2	3	15
2/4/2009	2009	2	4	98
2/5/2009	2009	2	5	42
2/6/2009	2009	2	6	47
2/7/2009	2009	2	7	19
2/8/2009	2009	2	8	74
2/9/2009	2009	2	9	30
2/10/2009	2009	2	10	24
2/11/2009	2009	2	11	85
2/12/2009	2009	2	12	71
2/13/2009	2009	2	13	100
2/14/2009	2009	2	14	77
2/15/2009	2009	2	15	26
2/16/2009	2009	2	16	89
2/17/2009	2009	2	17	11
2/18/2009	2009	2	18	98
2/19/2009	2009	2	19	100
2/20/2009	2009	2	20	76
2/21/2009	2009	2	21	32
2/22/2009	2009	2	22	41
2/23/2009	2009	2	23	87
2/24/2009	2009	2	24	23
2/25/2009	2009	2	25	33
2/26/2009	2009	2	26	44
2/27/2009	2009	2	27	90
2/28/2009	2009	2	28	69
3/1/2009	2009	3	1	84
3/2/2009	2009	3	2	61
3/3/2009	2009	3	3	75
3/4/2009	2009	3	4	95
3/5/2009	2009	3	5	59
3/6/2009	2009	3	6	81
3/7/2009	2009	3	7	59
3/8/2009	2009	3	8	80
3/9/2009	2009	3	9	14
3/10/2009	2009	3	10	68
3/11/2009	2009	3	11	98
3/12/2009	2009	3	12	20
3/13/2009	2009	3	13	92
3/14/2009	2009	3	14	32
3/15/2009	2009	3	15	39
3/16/2009	2009	3	16	43
3/17/2009	2009	3	17	65
3/18/2009	2009	3	18	14

[Zubair_Muhammad · Accepted solution]

Hi @Aliafshari

When you select a YEAR, a MONTH and a DAY, result would be a single date

SELECTEDVALUE pulls that date

see the attached file with your sample data

[Zubair_Muhammad]

Hi @Aliafshari

Try this MEASURE

Sum_Before_Selected_Day =
CALCULATE (
    SUM ( TableName[Quantity] ),
    FILTER (
        ALL ( TableName ),
        TableName[Date] < SELECTEDVALUE ( TableName[Date] )
    )
)

[Aliafshari]

Hi Zubair_Muhammad

Thanks for youre notice, Youre measure only depend on Date but I really want to first select Year, month and then day, how to add this filters in youre measure?

[Zubair_Muhammad · Accepted solution]

Hi @Aliafshari

When you select a YEAR, a MONTH and a DAY, result would be a single date

SELECTEDVALUE pulls that date

see the attached file with your sample data

[Aliafshari]

Thank you Zubair_Muhammad 

[Aliafshari]

Sorry  Zubair_Muhammad 

One more question, Consider we add a column name as Main account, how to sum before a day for each of main account in a table?

Date          Main account        Quantity

 **                      **                     **

[Zubair_Muhammad]

HI @Aliafshari

In that case use this MEASURE. Then drag it to the TABLE visual along with the account names

Sum_Before_Selected_Day =
CALCULATE (
    SUM ( TableName[Quantity] ),
    FILTER (
        ALLEXCEPT ( TableName, TableName[Main Account] ),
        TableName[Date] < SELECTEDVALUE ( TableName[Date] )
    )
)

[Zubair_Muhammad · Accepted solution]

@Aliafshari

See the revised attached file here

[Aliafshari]

Thanks for the swift reply!