Forum Discussion

Tony_PBI's avatar
Tony_PBI
New Member
7 years ago
Solved

Flexible time series issue

Hi all

 

I am facing an issues to create a flexible time series data table. 

My seranio is as below: 

 

Input: several tasks information, 1-start date, 2-end date, 3-cost per day;

Expected outputs: Base timeline is a flexible time-series: (Today()-365, Today()+365), match start and end data for each task, match everyday cost on the flexible time-series, then sum cost for all tasks. 

 

Previously, I am using VBA to achieve this function. But I didn't find "for" loop function in DAX. 

Could anyone give some suggestions on how to achieve this function? Appreciate your feedback!

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Tony_PBI 

    There are 5 conditions as below

    “flexible time-series”  vs “start and end data for each task”

    Create measures

    period start = TODAY()-365

period end = TODAY()+365

flag =
SWITCH (
    TRUE (),
    [period end] <= MAX ( Table1[end date] )
        && [period start] >= MAX ( Table1[start date] ), "c1",
    [period end] <= MAX ( Table1[end date] )
        && [period end] >= MAX ( Table1[start date] )
        && [period start] < MAX ( Table1[start date] ), "c2",
    [period end] > MAX ( Table1[end date] )
        && [period start] >= MAX ( Table1[start date] )
        && [period start] < MAX ( Table1[end date] ), "c3",
    [period start] > MAX ( Table1[end date] ), "c4",
    [period end] < MAX ( Table1[start date] ), "c5"
)

    
total days = 
SWITCH (
    [flag],
    "c1", DATEDIFF ( [period start], [period end], DAY ),
    "c2", DATEDIFF ( MAX ( Table1[start date] ), [period end], DAY ),
    "c3", DATEDIFF ( [period start], MAX ( Table1[end date] ), DAY ),
    "c4", 0,
    "c5", 0
)

total cost = SUM(Table1[cost])*[total days]

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

    Share some data in a format that can be pasted in MS Excel and show your expected result.

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Tony_PBI 

    There are 5 conditions as below

    “flexible time-series”  vs “start and end data for each task”

    Create measures

    period start = TODAY()-365

period end = TODAY()+365

flag =
SWITCH (
    TRUE (),
    [period end] <= MAX ( Table1[end date] )
        && [period start] >= MAX ( Table1[start date] ), "c1",
    [period end] <= MAX ( Table1[end date] )
        && [period end] >= MAX ( Table1[start date] )
        && [period start] < MAX ( Table1[start date] ), "c2",
    [period end] > MAX ( Table1[end date] )
        && [period start] >= MAX ( Table1[start date] )
        && [period start] < MAX ( Table1[end date] ), "c3",
    [period start] > MAX ( Table1[end date] ), "c4",
    [period end] < MAX ( Table1[start date] ), "c5"
)

    
total days = 
SWITCH (
    [flag],
    "c1", DATEDIFF ( [period start], [period end], DAY ),
    "c2", DATEDIFF ( MAX ( Table1[start date] ), [period end], DAY ),
    "c3", DATEDIFF ( [period start], MAX ( Table1[end date] ), DAY ),
    "c4", 0,
    "c5", 0
)

total cost = SUM(Table1[cost])*[total days]

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.