Forum Discussion

IF's avatar
IF
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

3 month rolling count

Hi,

 

I want to count the number of records based on the field. I have type and date tables that I do selection from those slicers. If I select 06.2020, I want to count the number of the records for 06,05,04.2020. I used the following measure, but it doesn't work:

All = var a=SELECTEDVALUE('month'[date], MAX('month'[date]))
return CALCULATE(count(Main[value]),DATESINPERIOD(Main[date],a,-3,MONTH))
Table:Main

type

dateselection

date

value

a

06.2020

06.04.2020

5

a

06.2020

06.14.2020

21

a

05.2020

05.04.2020

3

a

03.2020

03.24.2020

8

a

03.2020

03.04.2020

8

a

02.2020

02.24.2020

9

b

06.2020

06.04.2020

5

b

06.2020

06.14.2020

21

b

05.2020

05.04.2020

3

b

04.2020

04.24.2020

8

b

03.2020

03.04.2020

8

b

02.2020

02.24.2020

3

b

01.2020

01.24.2020

2

b

01.2020

01.26.2020

2

 
Table: Month
monthselectiondate
06.202006.30.2020
05.202005.31.2020
04.202004.30.2020
03.202003.31.2020
02.202002.29.2020
01.202001.31.2020
 
Table: type
type
a
b

 

Second issue: I want to count certain machines. If the difference between the end of the selected month and Main[date] is more than 5 days and less than 36 days, then that info should be counted. This is the second measure that I want to have.

 
thanks in advance
 
 
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  IF ,

     

    Create 2 measures as below:

    total moving count = 
CALCULATE(COUNTROWS('Main'),FILTER(ALL(Main),'Main'[dateselection]<=SELECTEDVALUE('Month'[Month])&&'Main'[dateselection]>=CALCULATE(MIN('Month'[Month]),DATEADD('Month'[Month],-2,MONTH))))
    selected type moving count = 
CALCULATE(COUNTROWS('Main'),FILTER(ALL(Main),'Main'[dateselection]<=SELECTEDVALUE('Month'[Month])&&'Main'[dateselection]>=CALCULATE(MIN('Month'[Month]),DATEADD('Month'[Month],-2,MONTH))&&'Main'[type]=SELECTEDVALUE('Type'[type])))

    And you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

     

     

3 Replies

  • timg's avatar
    timg
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Hi IF,

    Regarding your first question, perhaps this logic will do?

    MovingCount = 
CALCULATE (
    COUNTROWS(FactSales),
    DATESINPERIOD (
        DimDate[Date],
        LASTDATE ( DimDate[Date] ),
        -3,
        MONTH
    )
)

    This allows me to get the rolling record count of 3 months in my test set.

    Hope it helps!

     

    Regards,

    Tim 

    • IF's avatar
      IF
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      Hi,

      Thanks for your response!

      I tried but didn't work for me. it brings data only for the selected month. I also want to count the values if they exist. It is possible that then it should not count.

      Thanks again! Regards,

      • v-kelly-msft's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi  IF ,

         

        Create 2 measures as below:

        total moving count = 
CALCULATE(COUNTROWS('Main'),FILTER(ALL(Main),'Main'[dateselection]<=SELECTEDVALUE('Month'[Month])&&'Main'[dateselection]>=CALCULATE(MIN('Month'[Month]),DATEADD('Month'[Month],-2,MONTH))))
        selected type moving count = 
CALCULATE(COUNTROWS('Main'),FILTER(ALL(Main),'Main'[dateselection]<=SELECTEDVALUE('Month'[Month])&&'Main'[dateselection]>=CALCULATE(MIN('Month'[Month]),DATEADD('Month'[Month],-2,MONTH))&&'Main'[type]=SELECTEDVALUE('Type'[type])))

        And you will see:

         

        For the related .pbix file,pls see attached.

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my post as a solution!