calendar cannot be blank

[waleed111]

when i use this formula:

Date = CALENDAR('List View'[start date], 'List View'[end date])

it show me this error:

The start date or end date in Calendar function can not be Blank value.

[Anonymous]

Hi @waleed111 ,

Calendar() returns a table. As @Fowmy , @amitchandak said , use MIN() , MAX() to specify the date like this:

Date Table =
CALENDAR ( MIN ( 'List View'[Start Date] ), MAX ( 'List View'[End Date] ) )

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[amitchandak]

@waleed111 , if you are taking from other tables it needs to min /max

Date = CALENDAR(Min('List View'[start date]), max('List View'[end date]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[waleed111]

now it show me this error

[Fowmy]

@waleed111 

Try it this way, Hope you are adding a new calendar table based on List View Table

Date = CALENDAR(MIN('List View'[start date]),MAX( 'List View'[end date]))

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[waleed111]

after i use your solution it show me this error: Column 'start date' in table 'List View' cannot be found or may not be used in this expression.

[Fowmy]

@waleed111 

As per your code, you are trying to create a calendar table by supplying the Start Date and End Date from another table. That particular table should contain valid dates in both the columns. 

Do provide an explanation of what you are trying to achieve 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn 

[waleed111]

i use measure not colomn in calendar arguments

[Fowmy]

@waleed111 

Go to Modling Tab, click on New Table and paste the code I shared 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[waleed111]

i use measure not colomn therefore max and min not work with measure may be

 

[amitchandak]

@waleed111 , If I have a list table I can calendar using the column  that table has

Calendar(min(List View[Start date]), Max(List View[End Date]))

Check the Attached Pbix , How I done with My tables

[Fowmy]

@waleed111 

Go to Modeling Tab click on New Table and paste this code, Your measures Start Date and End Date will be accepted.
Please show how those measures are calculated.

Date = CALENDAR('List View'[start date],'List View'[end date])

I did an experiment creating a calendar table with Measures

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[MFelix]

@Fowmy 

Measures are accepted as values in tables, however since measure are based on context, the result is not dinamic, so if you have two measures with MIN(Table[Date]) and MAX(Table[DAte]) this values will always pick up the maximum and minimum value of the table that you refer to, they are no dinamic. 

To my experience ussualy when people use measure in table it's because they want the dinamic part of the measure so when they select the slicer or change a parameter or something like that the table is dinamic, and that doesn't happen because as you know measures a context base so when you pick up a measure it get the table context and not the row context so the values are always the same and don't change no matter what you do with the other context settings like slicers, filters, or similar.

That's the reason why I refered that measures cannot be used, better said measures should not be used in a table creation.

But great answer.

[Fowmy]

@MFelix 

I absolutely agree on your points and am fully aware of it as well. 

I am not sure about @waleed111  purpose here, very little information provided to cater a solution. 

if he is creating a standard calendar, it's not the proper way either. 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[waleed111]

when i make it table it work but when i make it as colomn it not work why?

[MFelix]

Hi @waleed111 ,

The syntax that you have return several values so you cannot add it has a column to another table because when add columns you are working ar row level context so one row one value.

Using that syntax you would get something similar to this:

Column1	New Colum
a	Date1
a	Date2
a	Date3
b	Date1
b	Date2
b	Date3

What is the exact purpose you need to achieve?

[waleed111]

i want to make a date column from lowest value in the all tables to largest value

[MFelix]

Hi @waleed111 ,

You want to add a column with all the dates in possible in your table? Will this not get you blank values in some dates?

[waleed111]

You want to add a column with all the dates in possible in your table? yes

[MFelix]

Hi @waleed111 ,

Sorry for the followup questions but to what I uderstand you want to had all the dates in other tables, so if you have a table 1 that has let's assume values for the 1st week for January and you maximum date is the last day of january you table will have a lot of blanks values except for the date:

Table1 (before column)

Date	Value
01/01/2020	10
02/01/2020	30
03/01/2020	40
04/01/2020	50
05/01/2020	60

Table1 (after column)

Date	Value	New Date
01/01/2020	10	01/01/2020
02/01/2020	30	02/01/2020
03/01/2020	40	03/01/2020
04/01/2020	50	04/01/2020
05/01/2020	60	05/01/2020
		06/01/2020
		07/01/2020
		...
		29/01/2020
		30/01/2020
		31/01/2020

Is the representation above correct?

If that is the expected result, and once again I apologize why don't you use a calendar table to make the relationship with you table that will return the values that you need and make it more dinamic without the need to duplicate values. Also this type of merge is better made in Power Query than in dax because when you want to had new rows you need to make a new table.

[waleed111]

my question is: can i assign the CALENDAR function with a column and if i can what is the conditions?