Forum Discussion

sfn's avatar
sfn
Frequent Visitor
8 years ago
Solved

Normalizing Periods from Dates

Hi,   Does anyone have experience with normalizing date ranges to represent periods starting at n for a set of categories?   I have a lot of data on hours spent to execute specific projects, and ...
Need Help
  • Stachu's avatar
    Stachu
    8 years ago

    so you basically want to rank the dates for each category
    try this calculated column

    Column = 
VAR Cat = Table[Category]
RETURN
RANKX(FILTER(Table,Table[Category]=Cat),FIRSTDATE(Table[Date]),,ASC)
Anonymous's avatar
Anonymous
Not applicable
8 years ago

sfn,

Create a measure using DAX below and create a Line chart as shown in the following screenshot.

Measure = SUM(Table1[Quantity])+0



Regards,
Lydia

sfn's avatar
sfn
Frequent Visitor
8 years ago

Hi Lydia,

 

I've attempted this and it doesn't yield the result I'd like as it seems to fill the line-points without values as 0 values.

 

I'd like to use a measure that could;

take a specific category's first month and label it as 1, 

take a specific category's second month and label it as 2 etc.

 

Then display these labels as the x-axis.

 

Best regards,

Stian

  • Stachu's avatar
    Stachu
    Community Champion
    8 years ago

    so you basically want to rank the dates for each category
    try this calculated column

    Column = 
VAR Cat = Table[Category]
RETURN
RANKX(FILTER(Table,Table[Category]=Cat),FIRSTDATE(Table[Date]),,ASC)
    • sfn's avatar
      sfn
      Frequent Visitor
      8 years ago

      Yes, this resulted in what I was looking for - thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I getting the following error: 

       

      A single value for column 'WELL' in table 'BODS' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

       

      Here is my code

       

      Column =
      VAR Cat = BODS[WELL]
      RETURN
      RANKX(FILTER(BODS,BODS[WELL]=Cat),FIRSTDATE(BODS[REPORT_DATE]),,ASC)

       

      I'm terrible at DAX :(

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Super helpful, i had a similar issue.  Thanks for sharing!!