Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Generate a date range to make a line between 2 dates

Hello, 

 

I have a request in Power BI

 

I have a table like this : 

 

Date start Date end     Quantity 

1/12          31/12             17

 

I want this information with anothers informations that use Date table and make a line chart 

 

So i need that all the dates are represented because if i use only 1/12 and 31/12 i will have only 2 points in a date table with Date table

 

I did some transformations with Power Query and I extracted the number of days (30) 

 

So i want a table that start with 1/12 and end with 31/12 and contains 17/30

 

Like this

1/12 0

2/12 17/30

3/12 17/30 (and maybe **bleep** to have17/30+17/30)

4/12 17/30 (and maybe **bleep** to have 17/30+17/30+17/30)

...

31/12 17

 

Do you know if this possible ? 

 

Thank you in advance 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    Have you solved your problem?
    If not, please try this way:
    Use the DAX below to create a new table:

    Date table = CALENDAR(MAX('Table'[Date start]), MAX('Table'[Date end]))

    Then use the DAXs below to achieve your expected results:

    Value = 
VAR A = SELECTEDVALUE('Table'[Quantity])
VAR B = CALCULATE(COUNTROWS('Date table'),ALLSELECTED('Date table')) - 1
RETURN
IF(
    'Date table'[Date].[Day] = 1,
    0,
    DIVIDE(A, B)
)
    Value2 = 
VAR A = SELECTEDVALUE('Table'[Quantity])
VAR B = CALCULATE(COUNTROWS('Date table'),ALLSELECTED('Date table')) - 1
VAR C =
IF(
    'Date table'[Date].[Day] = 1,
    0,
    DIVIDE(A, B)
)
VAR D = 'Date table'[Date].[Day] - 1
RETURN
C * D

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    You need to create a calendar table. You can create one with a template like this:

    Calendar =
    ADDCOLUMNS (
    CALENDAR (DATE(2023, 1, 1), DATE(2024, 1, 1)),
    "Year", YEAR ([Date]),
    "Monthnumber", MONTH ([Date]),
    "YearMonthnumber", FORMAT ([Date], "YYYY/MM"),
    "YearMonthShort", FORMAT ([Date], "YYYY/mmm"),
    "MonthNameShort", FORMAT ([Date], "mmm"),
    "MonthNameLong", FORMAT ([Date], "mmmm"),
    "DayOfWeekNumber", WEEKDAY ([Date]),
    "DayOfWeek", FORMAT ([Date], "dddd"),
    "DayOfWeekShort", FORMAT ([Date], "ddd"),
    "Quarter", "Q" & FORMAT ([Date], "Q"),
    "YearQuarter", FORMAT ([Date], "YYYY") & "/Q" & FORMAT ([Date], "Q")
    )

    Or just use the calendarauto function. Then create a relationship between the date field in the calendar and the date field in your data table, and use the calendar date field as the X axis on your line chart. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Have you solved your problem?
    If not, please try this way:
    Use the DAX below to create a new table:

    Date table = CALENDAR(MAX('Table'[Date start]), MAX('Table'[Date end]))

    Then use the DAXs below to achieve your expected results:

    Value = 
VAR A = SELECTEDVALUE('Table'[Quantity])
VAR B = CALCULATE(COUNTROWS('Date table'),ALLSELECTED('Date table')) - 1
RETURN
IF(
    'Date table'[Date].[Day] = 1,
    0,
    DIVIDE(A, B)
)
    Value2 = 
VAR A = SELECTEDVALUE('Table'[Quantity])
VAR B = CALCULATE(COUNTROWS('Date table'),ALLSELECTED('Date table')) - 1
VAR C =
IF(
    'Date table'[Date].[Day] = 1,
    0,
    DIVIDE(A, B)
)
VAR D = 'Date table'[Date].[Day] - 1
RETURN
C * D

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