Forum Discussion

PBIUWO's avatar
PBIUWO
Icon for Helper III rankHelper III
5 years ago

Calculated Calendar Table and Relationships to Date Column?

I have 2 tables. 

1. Calculated Date Calendar table

2. Table with Orders, Order Scheduled Date, and Order Closed Date. 

 

I have a calculated date table that is Dynamic:

Calendar = ADDCOLUMNS(var startDate = MIN( 'Order'[Closed Month/Year] )
var endDate = MAX( 'Order'[Scheduled Month/Year] )
RETURN
CALENDAR( startDate, endDate),"Month-Year",FORMAT([Date],"MMM YY"),"Sort",YEAR([Date]) * 100 + MONTH([Date]),"Year", FORMAT([Date],"YYYY"))
 
 
I want to make a table by month-year to count how many Orders closed after the scheduled date. But I can only get it to show on the Scheduled Date and not the Closed Date. 

 Ex. 
Table of all orders
OrderScheduled DateClosed Date
123January 2020March 2020

 

Ideal State: Order Organized by Month

 Jan 2020Feb 2020Mar 2020
Orders Closed after Scheduled Date001

 

Current State: Order Organized

 Jan 2020Feb 2020Mar 2020
Orders Closed after Scheduled Date100

 

Need Help
Tutorial Requests

3 Replies

    • PBIUWO's avatar
      PBIUWO
      Icon for Helper III rankHelper III
      5 years ago

      Thank you,

      I have it gotten it to work. But now, whenever I try to filter anything that has Schedule Date > Closed Date. The numbers dissappear. 

      Do you know why?

       

      Also, does this mean that I should always delete instead of inactivating the relationships?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi PBIUWO ,

     

    You could following these steps:

    1. Create a calendar table:

     

    Calendar =
VAR _max =
    IF (
        MAX ( 'Order'[Scheduled Month/Year] ) > MAX ( 'Order'[Closed Month/Year] ),
        MAX ( 'Order'[Scheduled Month/Year] ),
        MAX ( 'Order'[Closed Month/Year] )
    )
VAR _min =
    IF (
        MIN ( 'Order'[Scheduled Month/Year] ) < MIN ( 'Order'[Closed Month/Year] ),
        MIN ( 'Order'[Scheduled Month/Year] ),
        MIN ( 'Order'[Closed Month/Year] )
    )
RETURN
    ADDCOLUMNS (
        CALENDAR ( _min, _max ),
        "Month Year", FORMAT ( [Date], "MMM YY" )
    )

     

    2.Use SUMMARIZE() to "connect" calendar table and year-month column:

     

    LIST =
SUMMARIZE ( 'Calendar', 'Calendar'[Month Year] )

     

    3.Count Closed after Scheduled

     

    Measure =
SUMX (
    FILTER ( 'Order', 'Order'[Month Year closed] = MAX ( 'LIST'[Month Year] ) ),
    'Order'[Closed > Scheduled]
) + 0

     

    My visualization looks like this:

    Here is the pbix file.

     

    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