Normalizing Periods from Dates

[sfn]

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 I would like to compare their evolution over time starting at the same period n - rather than their respective start dates in order to generate a comparable profile.

Cheers,

Stian

[Anonymous]

@sfn,

You can create date slicer and select specific date value in the slicer to filter your visuals. Or you can create measures if necessary.

Please share sample data of your table and post expected result here so that we can provide you proper methods.

Regards,
Lydia

[sfn]

@Anonymous,

Thank you for your response, Lydia!

Example data below and graphs showing original representation per month and how I would like to represent it aligned at Start or aligned at Start and End points. 

Category	Date	Quantity
Category 1	01.01.2015	5
Category 1	01.02.2015	7
Category 1	01.03.2015	12
Category 1	01.04.2015	8
Category 2	01.02.2015	3
Category 2	01.03.2015	5
Category 2	01.04.2015	8
Category 2	01.05.2015	12
Category 2	01.06.2015	14
Category 2	01.07.2015	7
Category 3	01.03.2015	4
Category 3	01.04.2015	2
Category 3	01.05.2015	2
Category 3	01.06.2015	8
Category 4	01.05.2015	3
Category 4	01.06.2015	2
Category 4	01.07.2015	7
Category 4	01.08.2015	9
Category 4	01.09.2015	3
Category 4	01.10.2015	3
Category 4	01.11.2015	8
Category 5	01.06.2015	7
Category 5	01.07.2015	6
Category 5	01.08.2015	7
Category 5	01.09.2015	7

Any known means to achieve something like this?  

Best regards,

Stian

[Anonymous]

@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]

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 · Accepted solution]

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]

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

[Anonymous]

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 😞

[sfn]

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