1

I am trying to dynamically get the dates, starting from begging of month to current date using SSAS Tabular - DAX, I have tried many date functions but I couldn't end up with solution, so is there any idea to share pls ?

whytheq
  • 34,466
  • 65
  • 172
  • 267
Melvin
  • 51
  • 4

2 Answers2

2

The following DAX function will return a table of DATES for each day from the start of the current month through today:

DatesMTD = CALENDAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), TODAY())
David Tansey
  • 5,813
  • 4
  • 35
  • 51
2

To be able to filter the dates for the first 10 days, create a calculated column that identifies as a date in this range

1st to 10th Date = If(Day([Date]) <11,1,0)

1st to 10th Date

This can then be used to either filter out the dates past the 10th of each month

Daryl Wenman-Bateson
  • 3,870
  • 1
  • 20
  • 37
  • Thx for response, one more question, I have this script to retrieve Sum of MTD for current and previous date, I have hard coded this, but I want something dynamic like Getdate in SQL WITH MEMBER [Measures].[LeadsCurrentMTD] AS SUM(MTD([Date].[Calendar].[Date].&[20210216]), [Measures].[Lead Count]) MEMBER [Measures].[LeadsPreviousMTD] AS SUM(MTD([Date].[Calendar].[Date].&[20210216]), [Measures].[Lead Count]) SELECT { [Measures].[LeadsCurrentMTD] ,[Measures].[LeadsPreviousMTD] } ON 0 FROM [EDW] ; – Melvin Mar 18 '21 at 15:31
  • This is hard to work through as a comment. Maybe create a new question, along with a small data example? – Daryl Wenman-Bateson Mar 18 '21 at 15:46