Forum Discussion

Yieutumajajaja's avatar
Yieutumajajaja
Frequent Visitor
1 year ago

Issue with DAX calendar table using min and max dates from a table I later connect

Hi everyone, I've got this issue.

I created a DAX calendar table using the min and max date from two columns ( VENDOR_CREATION_DATE and INVOICE_DATE) of a different table (Query1).

 

Calendar =
var maxstart=max(Query1[VENDOR_CREATION_DATE])
var maxstart2=max(Query1[INVOICE_DATE])
var max3= IF(maxstart>maxstart2,maxstart,maxstart2)

var minstart=MIN(Query1[VENDOR_CREATION_DATE])
var minstart2=MIN(Query1[INVOICE_DATE])
var min3= IF(minstart<minstart2,minstart,minstart2)
VAR Mycal = CALENDAR(min3,max3)

VAR FullCal =
    ADDCOLUMNS(
        Mycal,
        "Index",MONTH([Date]),
        "Quarter","Qtr "& QUARTER([date]),
        "Month",FORMAT([Date],"mmm"),
        "Year",FORMAT([Date],"yyyy"),
        "YearMon",FORMAT([Date],"mmm-yy"),
        "Month Year Sort Order", YEAR([Date]) & UNICHAR(MONTH([Date]) + 64),
        "PERIOD_END_DATE",EOMONTH([Date],0),
        "Year_Slicer",
        IF(
           AND(YEAR([Date])<=YEAR(TODAY()),
           YEAR([Min invoice date])<=YEAR([Date])),
            YEAR([Date])&"",
            "")
   )
RETURN
    FullCal
 
I connect  the Calendar to the Query 1  as 1 to many. I know all the dates will be included in the calendar table.

 

 
To test it works correct I put some data in a Matrix viz and I see the below   
 
Year is from the DAX Calendar table
the INVOICE_DATE from the Query1

 

I have converted all dates to proper dates in the Query editor and in the SQL query, thinking that some how they could be just formatted and they could be Date/time.

 

Do you have any idea what I am missing here?

 

Otherwise I will then create the Calendar table in the Query editor

 

 

 

Thanks in advance for your help

Need Help
Tips and Tricks

5 Replies

  • suparnababu8's avatar
    suparnababu8
    Icon for Super User rankSuper User
    1 year ago

    Hi Yieutumajajaja 

     

    In place of MIN and MAX you can try with FIRSTDATE and LASTDATE dax functions. 

     

    Calendar =
VAR maxstart = LASTDATE(Query1[VENDOR_CREATION_DATE])
VAR maxstart2 = LASTDATE(Query1[INVOICE_DATE])
VAR max3 = IF(maxstart > maxstart2, maxstart, maxstart2)

VAR minstart = FIRSTDATE(Query1[VENDOR_CREATION_DATE])
VAR minstart2 = FIRSTDATE(Query1[INVOICE_DATE])
VAR min3 = IF(minstart < minstart2, minstart, minstart2)

VAR Mycal = CALENDAR(min3, max3)

VAR FullCal =
    ADDCOLUMNS(
        Mycal,
        "Index", MONTH([Date]),
        "Quarter", "Qtr " & QUARTER([Date]),
        "Month", FORMAT([Date], "mmm"),
        "Year", FORMAT([Date], "yyyy"),
        "YearMon", FORMAT([Date], "mmm-yy"),
        "Month Year Sort Order", YEAR([Date]) & UNICHAR(MONTH([Date]) + 64),
        "PERIOD_END_DATE", EOMONTH([Date], 0),
        "Year_Slicer",
        IF(
            AND(YEAR([Date]) <= YEAR(TODAY()), YEAR(minstart2) <= YEAR([Date])),
            YEAR([Date]) & "",
            ""
        )
    )
RETURN
    FullCal

     

    Let me know If it works.

     

     

    • Yieutumajajaja's avatar
      Yieutumajajaja
      Frequent Visitor
      1 year ago

      Thanks for your reply, I changed the formula and the result is the same. 

      I guess it has to do with the connection. 

      Calendar table was created using (VENDOR_CREATION_DATE and INVOICE_DATE)

      The actual connection was done with (GL_DATE). 

      Maybe that has somehow something to do. What I see in the connection file is two Date type columns connected. 

      Regards

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Yieutumajajaja 

     

    Thank you very much suparnababu8 for your prompt reply.

     

    For your question, here is the method I provided:

     

    Here's some dummy data

     

    "Query1"

     

    Dax

    Calendar = 
VAR maxstart = MAX(Query1[VENDOR_CREATION_DATE])
VAR maxstart2 = MAX(Query1[INVOICE_DATE])
VAR max3 = IF(maxstart > maxstart2, maxstart, maxstart2)

VAR minstart = MIN(Query1[VENDOR_CREATION_DATE])
VAR minstart2 = MIN(Query1[INVOICE_DATE])
VAR min3 = IF(minstart < minstart2, minstart, minstart2)

VAR Mycal = CALENDAR(min3, max3)

VAR FullCal =
    ADDCOLUMNS(
        Mycal,
        "Index", MONTH([Date]),
        "Quarter", "Qtr " & QUARTER([Date]),
        "Month", FORMAT([Date], "mmm"),
        "Year", FORMAT([Date], "yyyy"),
        "YearMon", FORMAT([Date], "mmm-yy"),
        "Month Year Sort Order", YEAR([Date]) & UNICHAR(MONTH([Date]) + 64),
        "PERIOD_END_DATE", EOMONTH([Date], 0),
        "Year_Slicer",
        IF(
            AND(YEAR([Date]) <= YEAR(TODAY()), YEAR(maxstart2) <= YEAR([Date])),
            YEAR([Date]) & "",
            ""
        )
    )
RETURN
    FullCal

     

    Make sure that the data type of all date columns is set to "Date" and not "Date /Time" or any other format.

     

     

    The relationship between two tables.

     

     

    Here is the result.

     

     

    Regards,

    Nono Chen

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

     

     

    • Yieutumajajaja's avatar
      Yieutumajajaja
      Frequent Visitor
      1 year ago

      Thanks v-nuoc-msft,

      From what I can see in your example the connection created is a 1 to many, being the many the Calendar Table which has been created to be used as a Dimension table.

      In your solution I see as well blanks which shouldn't be possible if the Calendar table contains all the days 

      (the dates in GL DATE will be always between the dates of the other two tables)

      Whether it can work in  your example I am not sure if that is going to do for my purpose.

      Thanks so much again for your reply

      Regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Yieutumajajaja 

         

        If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

         

        Regards,

        Nono Chen

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