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 produced by that range but that's not aligned with what i should get. What i should get is  an output something like this

for example, on the "selectedDate" range slicer, i'm choosing 24 February 2018, the result for year 2017 should be in the selected date of 24 February 2017 and for year 2016, it should be in the selected of 24 February 2016. I'm not sure what measure should i change and how to proceed. The data for "no of orders by selected month" should be just the total count of those in Jan and Feb in every year only based on below table.


List of Measures used-

No of Orders by Selected Month =
VAR _selectedmonthselecteddate = [test]
RETURN CALCULATE([Order Count], 'Calendar'[MonthNum] <= _selectedmonthselecteddate)
 
test = CALCULATE(MAX('Calendar'[MonthNum]), Filter('Calendar',[MonthNum] = Month(_Formulas[SelectedDate])))

 

SelectedDate =
Var _SelectedDate = Max('Calendar'[Date])
Return _SelectedDate

This is the PBI file that i'm working on. Hope you all have a nice day ahead, thank you!!
amitchandak

 

 

  • 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

3 Replies

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Community Support
    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

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous ,

    For of if you take a joined date table data will merge in one single date

    So use an independent date in the slicer

     

    //Date1 is independent Date table, Date is joined with Table
    new measure =
    var _max = format(maxx(allselected(Date1),Date1[Date]), "mmdd")
    return
    calculate( sum(Table[Value]), filter('Date',format( 'Date'[Date] "mmdd") =_max))

     

    Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

  • 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