Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

make a calendar using the dax function calendar

Hello,

 

I have a project table which contains projectid, startdate and enddate.

So using a slicer, I would like to select a particular project and based on that I would like to obtain a calendar table for each particular project.

 

ex: 

projectid    startdate           enddate

1                  2020-06-01     2020-06-30

2                  2020-03-01     2020-12-31

3                   2020-01-01    2020-02-01

and so on.

 

Therefore, if I select the projectid=1, I would like to obtain a date table from 2020-06-01 until 2020-06-30.

 

 

I have tried the following DAX but it does not work

 

Calendar based on Data = CALCULATETABLE(
CALENDAR(
FIRSTDATE('Project''s List'[WBS1StartDate]),
LASTDATE('Project''s List'[WBS1EstCompletionDate])),
FILTER('Project''s List'[ProjectSkey]=SELECTEDVALUE('Project''s List'[ProjectSkey]))
 
So, if I select a project using a slicer, I should have only one startdate and one enddate, then on date column per selected project.
Does someone know how to do that.
Regards,

 

 

Need Help

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi Anonymous 

    First, you should know that:

    1. Calculation column/table not support dynamic changed based on filter or slicer.
    2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result in a visual.

    https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

     

    Second, for your case, you just could just create a calendar table and create a meausre as below:

    Measure = CALCULATE(COUNTA('Table'[projectid]),FILTER('Table',MAX('calendar'[Date])>='Table'[startdate]&&MAX('calendar'[Date])<='Table'[enddate]))

    Then drag date field and this measure into a table visual

    Result:

     

     

    here is a similar blog, you could have a look:

    https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

     

    and here is sample pbix file, please try it.

     

     

    Regards,

    Lin

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    Hey Anonymous ,

     

    I have to admit that I do not fully understand why you want a calendar table per project.

     

    Nevertheless, it's not possible to dynamically create a table based on a slicer selection, that then by itself is used in another slicer.

     

    Nevertheless, you can achieve what you want inside a measure by creating a table filter that is based on the selection of one or more selected projects, by using a DAX statement that is similar to the one below:

    measure = 
var DateStart = MIN('projecttable'[startdate])
var DateEnd = MAX('projecttable'[startdate])
var tDates = GENERATESERIES(DateStart , 1 , DateEnd)
return
CALCULATE(
	<a numeric expression>
	, TREATAS(tDates , 'CalendarTable'[DateColumn])
	)

     Hopefully, this provides some new ideas.

     

    Regards,

    Tom

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello,

       

      It is what I have in mind, to create some kind of dynamic table, starting with a date column which the min date is equal to the startdate of the project, and the end date of the project. The next column will be the day number, then the total project's budget, then the project's revenue for each day, an so on.  (see the photo).

       

      If you have some idea how you could do that, please let me know.