Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

previous month value

Hi,

 

 

i am using below measure for previous month but when nothning is selected the card visual is showing (Blank).  i want to show constant value for previous month and change accordeingly month selection in slicer. 

please guide

Previous month Count = 
VAR __currentDate = EOMONTH ( MAX ( Sheet1[date] ), -1 )
RETURN 
CALCULATE ( 
    COUNTROWS ( Sheet1 ), 
    DATESBETWEEN ( Sheet1[date], 
    EOMONTH ( __currentDate, -1 ) + 1, __currentDate ) 
)

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    Due to I don't know your data model, I build a sample to have a test.

    Sample is from 2020/11/01 to 2021/03/04

    I build a YearMonth column in my sample table.

    YearMonth = YEAR('Sheet1'[Date])*100+MONTH('Sheet1'[Date])

    Build a slicer table.

    Slicer = VALUES('Sheet1'[YearMonth])

    Try IF and Isfiltered function in measure.

    Measure:

    Measure = 
VAR _SEl = SELECTEDVALUE(Slicer[YearMonth])
VAR _Previous = MAXX(FILTER(ALL(Slicer),Slicer[YearMonth]<_SEl),Slicer[YearMonth])
VAR _CURRENT = YEAR(TODAY())*100+MONTH(TODAY())
Return
IF(ISFILTERED(Slicer[YearMonth]),CALCULATE(COUNTROWS(Sheet1),FILTER(ALL(Sheet1),Sheet1[YearMonth] = _Previous)),CALCULATE(COUNTROWS(Sheet1),FILTER(ALL(Sheet1),Sheet1[YearMonth] = _CURRENT-1)))

    Result:

    By default it will show values in previous month of the current month.

    If I select slicer, it will show values of previous month according to selection.

    Best Regards,

    Rico Zhou

     

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

     

     

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak thanks 

      but it whows when i select option in slicer not without that

       

      i wish have visual which show result even i have not selected any option in slicer

       

      Please guide me 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous 

        Due to I don't know your data model, I build a sample to have a test.

        Sample is from 2020/11/01 to 2021/03/04

        I build a YearMonth column in my sample table.

        YearMonth = YEAR('Sheet1'[Date])*100+MONTH('Sheet1'[Date])

        Build a slicer table.

        Slicer = VALUES('Sheet1'[YearMonth])

        Try IF and Isfiltered function in measure.

        Measure:

        Measure = 
VAR _SEl = SELECTEDVALUE(Slicer[YearMonth])
VAR _Previous = MAXX(FILTER(ALL(Slicer),Slicer[YearMonth]<_SEl),Slicer[YearMonth])
VAR _CURRENT = YEAR(TODAY())*100+MONTH(TODAY())
Return
IF(ISFILTERED(Slicer[YearMonth]),CALCULATE(COUNTROWS(Sheet1),FILTER(ALL(Sheet1),Sheet1[YearMonth] = _Previous)),CALCULATE(COUNTROWS(Sheet1),FILTER(ALL(Sheet1),Sheet1[YearMonth] = _CURRENT-1)))

        Result:

        By default it will show values in previous month of the current month.

        If I select slicer, it will show values of previous month according to selection.

        Best Regards,

        Rico Zhou

         

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