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 ...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Anonymous ;

    You could create a measure such as:

    SelectedDate = 
Var _SelectedDate = CALCULATE( Max('Calendar'[Date]),ALLSELECTED('Calendar'))
Return CALCULATE(MAX('Calendar'[Date]),FILTER('Calendar',MONTH([Date])=MONTH(_SelectedDate)&&DAY([Date])=DAY(_SelectedDate)))
    No of Orders by Selected Month = 
Var _SelectedDate = CALCULATE( Max('Calendar'[Date]),ALLSELECTED('Calendar'))
var _piryear= CALCULATE(  [Order Count],FILTER(ALL('Calendar'),YEAR([Date])=MAX('Calendar'[Year])&& MONTH([Date])<=MONTH(_SelectedDate)))
return IF(YEAR(MAX('Calendar'[Date]))=YEAR(_SelectedDate), CALCULATE(  [Order Count],FILTER(ALL('Calendar'),YEAR([Date])=MAX('Calendar'[Year])&& [Date]<=_SelectedDate)),_piryear)

    the final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi amitchandak , thank you for replying, i really do appreaciate it.
Just had a simple question, what does it mean by independent date table by your definition? From my POV, i did used an independent date table which is "Calendar" table. Do correct me if i'm wrong, thank you!