Forum Discussion

Puja's avatar
Puja
Icon for Helper III rankHelper III
3 years ago
Solved

Previous Month Flag

Hi ,

I need some help with Previous month flag. When user selecta a date Ex: 3/1/2020, the result should be pevious month (2/1/2020) 

Dates Test data.

1/1/2020
2/1/2020
3/1/2020
4/1/2020
5/1/2020
6/1/2020
7/1/2020
8/1/2020
9/1/2020
10/1/2020
11/1/2020
12/1/2020
1/1/2021
2/1/2021
3/1/2021
4/1/2021
5/1/2021

 

 

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    Hi Puja 

    1) create a table like:

    Table2 = Table1

    2) plot a table visual with Table1[Date] column and a measure like:

    Measure = 
IF(
    SELECTEDVALUE(Table1[Date]) = EDATE(SELECTEDVALUE(Table2[Date]),-1),
    1,0
)

    it worked like:

     

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi, 

    I am not sure how your datamodel looks like, but please check the below picture and the attached pbix file.

     

     

     

    Expected result measure: =
VAR _selectedmonthyear =
    MAX ( Data[Date_test] )
VAR _selectedday =
    DAY ( _selectedmonthyear )
VAR _prevendofmonthdate =
    EOMONTH ( _selectedmonthyear, -1 )
RETURN
    DATE ( YEAR ( _prevendofmonthdate ), MONTH ( _prevendofmonthdate ), _selectedday )

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi, 

    I am not sure how your datamodel looks like, but please check the below picture and the attached pbix file.

     

     

     

    Expected result measure: =
VAR _selectedmonthyear =
    MAX ( Data[Date_test] )
VAR _selectedday =
    DAY ( _selectedmonthyear )
VAR _prevendofmonthdate =
    EOMONTH ( _selectedmonthyear, -1 )
RETURN
    DATE ( YEAR ( _prevendofmonthdate ), MONTH ( _prevendofmonthdate ), _selectedday )
    • Puja's avatar
      Puja
      Icon for Helper III rankHelper III
      3 years ago

      Hi Jihwan_Kim , Thanks a lot for your solution. It is working as expected.

      How can I referance your flag in the below measure.

      The ask from the user is when he selects a date , it should show previous month actuals. With TREATAS, i am not able to get the correct results.(I have to use TREATAS because there is no relation b/w Date and actuals tables in AAS model)

      Actual = CALCULATE([Actual], TREATAS(SUMMARIZE(Forecast,Forecast[Year SF],Forecast[Month SF]),
      DimDate[Year],DimDate[Month]))

       

      Thank u

       

       

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    Hi Puja 

    1) create a table like:

    Table2 = Table1

    2) plot a table visual with Table1[Date] column and a measure like:

    Measure = 
IF(
    SELECTEDVALUE(Table1[Date]) = EDATE(SELECTEDVALUE(Table2[Date]),-1),
    1,0
)

    it worked like: