Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Getting different data based on a Range column.

Hi All, I have some issue regarding getting different set of data by using date measure.    This is the PBI file that i'm working on.  Based on above figure, this is the range, and the data ...
  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    Hi Anonymous,

     

    You may try this solution.

    1 Modify your Selected Date as follows

    SelectedDate =
VAR _SelectedDate =
    MAX ( 'Calendar'[Date] )
VAR maxDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
RETURN
    DATE ( YEAR ( _SelectedDate ), MONTH ( maxDate ), DAY ( maxDate ) )

     

    2 Create another Measure

    OrdersBySelectedMonth =
VAR selectedMaxDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
RETURN
    CALCULATE (
        [Order Count],
        FILTER ( 'Calendar', MONTH ( 'Calendar'[Date] ) <= MONTH ( selectedMaxDate ) )
    )

     

    The result looks like this.

     

    Also, attached the pbix file as reference.

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

     

    Best Regards,                                                                                                                                                                    Community Support Team _ Caiyun

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi Anonymous 
Please refer to sample file with the solution https://we.tl/t-JBXwFiPLen

Order Count = 
VAR LastSelectedDate = MAXX ( ALLSELECTED ( 'Calendar'[Date] ), 'Calendar'[Date] )
VAR SelectedDay = DAY ( LastSelectedDate )
VAR SelectedMonth = MONTH ( LastSelectedDate ) 
VAR CurrentYear = YEAR ( MAX ( 'Calendar'[Date] ) )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( Sales[SalesOrderNumber] ),
        KEEPFILTERS ( 'Calendar'[MonthNum] = SelectedMonth ),
        FILTER ( 'Calendar', DAY ( 'Calendar'[Date] ) =  SelectedDay )
    )
RETURN
    Result