Calendar

[Anonymous]

 I have 7 tables , each tables has various rows and columns. One column is Dates ( in each table ).

I want to create a calendar using DAX that picks up the MAX from these tables columns. 

The min value is picked up from another table. 

any suggestion ?

[Anonymous · Accepted solution]

Hi @Anonymous 

Please correct me if I wrongly understood your issue .

You need to return the Max date from these multiple tables and then return the MIX date from these multiple tables .Then use the two dates to create a new Calendar date table .

Create a table with the following formula .

Calendar Date =
var _MIN=MINX({MIN('Table 1'[Date 1]),MIN('Table 2'[Date 2]),MIN('Table 3'[Date 3]),MIN('Table 4'[Date 4])},[Value])
var _MAX=MAXX({MAX('Table 1'[Date 1]),MAX('Table 2'[Date 2]),MAX('Table 3'[Date 3]),MAX('Table 4'[Date 4])},[Value])
return CALENDAR(_MIN,_MAX)

And the final result is as shown(Max date is 2021-04-05 and Min date is 2021-01-01) :

[value] is used to return some values in the previous {}, Take the above formula as an example: {MIN('Table 1'[Date 1]),MIN('Table 2'[Date 2]),MIN('Table 3'[Date 3]),MIN('Table 4'[Date 4])},[Value] , here [value] is used to return the min date in the four tables, and the final result is 2021-01-01,2021-02-01,2021-03-01,2021-04-01 . Is this explanation clear ?

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

what does [value] refer to ?

[mahoneypat]

You can try an expression like this to do that.

Calendar =
VAR vMinDate =
    MIN ( Table1[Date1] )
VAR vMaxDate =
    MAXX (
        { MAX ( Table2[Date2] ), MAX ( Table3[Date3] ), MAX ( Table4[Date4] ) },
        //add more as needed
        [Value]
    )
RETURN
    CALENDAR ( vMinDate, vMaxDate )

Pat