Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calendar

 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 ?

Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    what does [value] refer to ?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.