Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dividing Values to a Constant Interval Date

Hello,   I'm working on a table which has these columns: - Date (1st March 2020 to 21st May 2020) - Amount - Name of Product   I want to track the changes in the dates. The logic is: 1st March...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

     

    Please refer to my .pbix file.

    Measure 3 = 
IF(
    SELECTEDVALUE(Sheet2[Weekday]) = SELECTEDVALUE('Table'[_Weekday]) && SELECTEDVALUE(Sheet2[Date]) > DATE(2020, 3, 7),
    DIVIDE( SELECTEDVALUE(Sheet2[Amount]), SELECTEDVALUE('Table'[_Amount] ) )
)

     

    Best regards,
    Lionel Chen

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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

I created some sample data on my desktop, and I hope the result is what you want.

 

Column = VAR TEST = CALCULATE(MIN('Table'[DATE]),ALL('Table'))
          RETURN  IF('Table'[DATE] = TEST,0,IF(WEEKDAY('Table'[DATE]) = WEEKDAY(TEST),1,2))
Measure 8 = DIVIDE(CALCULATE(SUM('Table'[VALUE]),'Table'[Column] = 1),CALCULATE(SUM('Table'[VALUE]),FILTER(ALL('Table'[DATE],'Table'[Column]),'Table'[Column] = 0)))

 

 

Please try.

Aiolos Zhao

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hello,

Anonymous v-lionel-msft 

Both answers didn't work Let me clarify myself I think I didn't express myself well.

 

My table is on the link as excel file ( https://bit.ly/2ZZbsCT ). the first week is my control week (1-7 march) and starting from 8th Mar, I want all sundays (8, 15, 22 Mar, etc) to be divided by 1st Mar, all thursdays (12, 19, 26 Mar, etc) to be divided by 5th March, etc.

 

By the way, I'm showing one of the product code as an ex. There are a lot more.

 

 

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Please refer to my .pbix file.

    Measure 3 = 
IF(
    SELECTEDVALUE(Sheet2[Weekday]) = SELECTEDVALUE('Table'[_Weekday]) && SELECTEDVALUE(Sheet2[Date]) > DATE(2020, 3, 7),
    DIVIDE( SELECTEDVALUE(Sheet2[Amount]), SELECTEDVALUE('Table'[_Amount] ) )
)

     

    Best regards,
    Lionel Chen

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      It seems working. Thanks a lot for your help!