Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Date Table for For the Day / For the Month

Hi

We have the colum name " Closing Date ".

Based on this date can we create a Data Table for " For the Day". and "For the Month".

WHich means the data what we need is from the date list, everyday it should show the data from for the day and for the month.

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    You can get the data for the day or the month by measure.

    Due to I don't know your table, I build a sample table with Close Date column and Data column to have a test.

    Build a Calendar table by dax.

    Slicer = ADDCOLUMNS(CALENDARAUTO(),"Month",MONTH([Date]))

    Measure:

    For the Day = 
Var _SelDate = SELECTEDVALUE(Slicer[Date])
Return
SUMX(FILTER('Table','Table'[Close Date]= _SelDate),'Table'[Data])
    For the Month = 
Var _SelMon = SELECTEDVALUE(Slicer[Month])
Return
SUMX(FILTER('Table',MONTH('Table'[Close Date])= _SelMon),'Table'[Data])

    Then build two table visuals and slicers to show the result.

    For the day:

    For the Month:

    You can download the pbix file from this link: Date Table for For the Day / For the Month

     

    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

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    5 years ago

    Please watch this video about calendar tables and then use a day or month offset = 0 to just select the current day or all the days for the current month as required.

    Watch this video 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    You can get the data for the day or the month by measure.

    Due to I don't know your table, I build a sample table with Close Date column and Data column to have a test.

    Build a Calendar table by dax.

    Slicer = ADDCOLUMNS(CALENDARAUTO(),"Month",MONTH([Date]))

    Measure:

    For the Day = 
Var _SelDate = SELECTEDVALUE(Slicer[Date])
Return
SUMX(FILTER('Table','Table'[Close Date]= _SelDate),'Table'[Data])
    For the Month = 
Var _SelMon = SELECTEDVALUE(Slicer[Month])
Return
SUMX(FILTER('Table',MONTH('Table'[Close Date])= _SelMon),'Table'[Data])

    Then build two table visuals and slicers to show the result.

    For the day:

    For the Month:

    You can download the pbix file from this link: Date Table for For the Day / For the Month

     

    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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

     

    Best Regards,

    Rico Zhou