make a calendar using the dax function calendar

[Anonymous]

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,

[v-lili6-msft · Accepted solution]

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]

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]

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.