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

[Yieutumajajaja]

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

[Anonymous]

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]

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]

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.

[suparnababu8]

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]

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