cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Counting Days in a column

Hey all,

its really hard to explain, so I'll try to show you what I want within the following table. What I want is a formula for the column "Number of Days".

As the table doesn't has values for every date, the formula for "Number of Days" has to refer to a calendar-table (I guess...), which is already created. Otherwise, you could say, that the date of the first apperance of a product could get the day number 1, but then I don't know how to do count the days that are missing in this table.

 Vol Date Product Number of Days 1 24.07.2022 A 1 2 24.07.2022 A 1 3 25.07.2022 A 2 2 26.07.2022 A 3 2 28.07.2022 A 5 1 29.07.2022 B 1

I hope this is understandable.

Thank you very much in advance!

1 ACCEPTED SOLUTION
Community Support

Hi, @phjz

You can't apply a measure to the x-axis of line chart.

You need to convert your original measure formula to calculated column formula.

``````Column_Number of days =
VAR basedate =
CALCULATE ( MIN ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Product] ) )
RETURN
DATEDIFF ( basedate, Table1[Date], DAY ) + 1
``````

Best Regards,
Community Support Team _ Eason

3 REPLIES 3
Impactful Individual

hello @phjz ,

``````Number of days =
VAR basedate = CALCULATE(MIN(Table1[Date]),ALLEXCEPT(Table1,Table1[Product]))
return
DATEDIFF(basedate,SELECTEDVALUE(Table1[Date]),DAY) +1``````

Frequent Visitor

That does work really good - thank you so much!

There is one last problem with this: I want to compare Product A and B using a line diagram, but I can't put the measure on the x-axis. When I try to type the formula in the field for a new column, it doesn't work. Do you have an idea for this?

Community Support

Hi, @phjz

You can't apply a measure to the x-axis of line chart.

You need to convert your original measure formula to calculated column formula.

``````Column_Number of days =
VAR basedate =
CALCULATE ( MIN ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Product] ) )
RETURN
DATEDIFF ( basedate, Table1[Date], DAY ) + 1
``````

Best Regards,
Community Support Team _ Eason

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors