New Column for rolling month with slicer

[IF]

Hi,

I could not make a new column to get the values for the last three months. I checked from other forms, but mine doesn't work.

I have the main table:

Type	datetext	date	value	order
a	04.2020	4.1.2020	5	4
a	04.2020	4.1.2020	4	4
a	03.2020	3.1.2020	3	3
a	02.2020	2.1.2020	2	2
a	01.2020	1.1.2020	1	1
b	04.2020	4.1.2020	15	4
b	04.2020	4.1.2020	14	4
b	03.2020	3.1.2020	13	3
b	02.2020	2.1.2020	12	2
b	01.2020	1.1.2020	11	1
a	12.2019	12.1.2019	1	0
b	12.2019	12.1.2019	10	0

I have also 2 more tables and use the slicers from there.

Type table

Type

a

b

Month table

DateT	Date	Order
03.2020	3.1.2020	3
02.2020	2.1.2020	2
01.2020	1.1.2020	1
04.2020	4.1.2020	4
12.2019	12.1.2019	0

I use Type[Type] and Month[DateT] fields for two different slicers.

I wanted to make a new column:

Column= CALCULATE(COUNTROWS('Table'),DATESINPERIOD('Month'[date],LASTDATE('Month'[date]),-3,MONTH))

It doesn't work.

I tried:

SUM ( 'Table'[value] ),  FILTER ( ALL ( 'Month'[Order]), 'Month'[Order] >= SELECTEDVALUE ( 'Month'[Order] ) - 2

&& 'Month'[Order] <= SELECTEDVALUE ( 'Month'[Order] ) ) )

It didn't work either.

For example; if 04.2020 is and a type is selected, I was expecting to get result 4 (April:2 counts + march 1 count + Feb. 1 count)

Similarly; if the type is not selected I expect to get the result 8(April:4 counts + march 2 count + Feb. 2 count).

 I really appreciate it if I get an answer.

[Anonymous]

Hi @IF ,

According to my understanding, you want to count the number of rows based on the selected value of Type and DateT via slicers, right?

First,The value of calculated column is stored in the row that will not change according to the user selection-- Calculated column could not be dynamic.So It's suggested to create measure instead.

Second, you need to delect the relationships between the three tables so that you can custom the cross-filter by measure.

Please try the following formula:

Flag =
VAR _type =
    SELECTEDVALUE ( 'Type'[Type] )
VAR _datetext =
    SELECTEDVALUE ( 'Month'[DateT] )
VAR _date =
    DATE ( RIGHT ( _datetext, 4 ), LEFT ( _datetext, 2 ), 1 )
VAR _v1 =
    IF (
        MAX ( 'Table'[type] ) = _type
            && MAX ( 'Table'[date] ) <= _date
            && MAX ( 'Table'[date] ) > EOMONTH ( _date, -3 ),
        1
    )
VAR _v2 =
    IF (
        MAX ( 'Table'[date] ) <= _date
            && MAX ( 'Table'[date] ) > EOMONTH ( _date, -3 ),
        1
    )
RETURN
    IF ( _type <> BLANK (), _v1, _v2 )

Then use the SUMX() for Card visual:

Count Rows = SUMX('Table',[Flag])

The final output is shown below:

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

[IF]

Hi 

It works, but I also want to get the sum of Table[Value] for the last 3months based on the selection (same as the other measure. 

I tried this:

SumValue =

var a = [Flag]

return

CALCULATE(SUM('Table'[value]),a)

It didn't work if I use the measure "flag". I am not really good with power bi. Can you help me to get the sum of value (Table[Value]) with the same logic?

[Anonymous · Accepted solution]

Hi @IF ,

Sorry for my late reply.

Please use this:

sum value =
CALCULATE ( SUM ( 'Table'[value] ), FILTER ( 'Table', [Flag] = 1 ) )

Best Regards,
Eyelyn Qin

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

[IF]

Perfect, thanks a lot!

[amitchandak]

@IF , best to have date table and use that. even if you have data of one day per month in your table

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,MONTH))

or

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date]),-1) ,-3,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[IF]

Hi,

I tried but didn't work. Can you check if I did smth wrong?

https://www.dropbox.com/s/zz0b6yiloezsduw/3%20month%20rolling%20second%20time.pbix?dl=0

Rolling 3 = CALCULATE(SUM('Table'[value]),DATESINPERIOD('Month'[Date],MAX('Month'[Date]),-3,MONTH))

Rolling 3 v2 = CALCULATE(SUM('Table'[value]),DATESINPERIOD('Month'[Date],EOMONTH(MAX('Month'[Date]), -1),-3,MONTH))

Thanks again!