Calculate number of months

[twister8889]

Hi guys,

I have a column Qty Month:

DATEDIFF(min(table1[Start Date]);max(table1[Month]); MONTH)

The problem is that datediff is not showing the sequence of Qty Month, like the image below:

The expected result is that the Qty Month Oct will be: 22, Nov 23, and Dec 2020 24, It is possible?

[dedelman_clng]

Hi @twister8889 -

As written, your formula is going to take the minimum start date *on each line*, not over the whole data set. You would have to write code like

QtyMonth = 
   DATEDIFF(
      CALCULATE( MIN( Table1[StartDate] ), ALL(Table1) ),      //Minimum over entire table
      MAX( Table1[Month]),
      MONTH
   )

Try that out and see if it works for you. Otherwise, please send a link to a copy of your report file so we can look at it further.

Hope this helps

David

[twister8889]

First of all, thank you for your answer..

@dedelman_clng  Here is my pbix

https://1drv.ms/u/s!AoDYwrtLrltJoFh-1Gs16CrjA8JI?e=0QDudS

[dedelman_clng]

@twister8889 - did my suggestion not work? Any further info you can provide will help you get to an answer more quickly.

[twister8889]

Sorry, not working, I tried but not working. I put my simple pbix.

Your suggestion is QtyMonth2

QtyMonth.2 =

DATEDIFF(

CALCULATE( MIN( Table1[Start Date] ), ALL(Table1) ), //Minimum over entire table

MAX( Table1[Month]),

MONTH

)

[dedelman_clng]

@twister8889 - by using DATEDIFF, Qty Month 2 is correct.

If you are not looking for the difference, in months, between the Minimum Start Date and "Month", please explain what you are actually trying to calculate. It sounds like it may be some kind of count, but I cannot infer anything else based on the information you have provided so far. Is it a cumulative number of months just based on what is in the table?

[twister8889]

OK.
I would like to count of months, starting with [Start Date].
So my date base, is [Start Date] until the last month, in this case, 2020-12-01, but not sequentially, because I don't have dates between 2019-09-01 and 2020-09-01. So in this case I need to show a Qty Month like 24 Months.

21 Months + 3 Months

[dedelman_clng]

Qty Month 3 =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER (
        ALL ( Table1 ),
        Table1[Month] <= MAX ( Table1[Month] )
    )
) + 1

[twister8889]

Hi,
The problem is that I can't count rows of the table, because in another scenario I have 24 lines in my table, but I have more range the dates, for example, the start date now is 2015-11-06, if I count the rows I have just 24 months, but the correct is QtyMonth 48 in the last Month.
I updated the pbix in one drive.

[dedelman_clng]

@twister8889  - 

I'm sorry but your requirements don't make any sense. Can you describe what you are trying to do in words not in hypothetical sets of data? What does your model look like? What are the use cases? What does the rest of your data look like?

[twister8889]

OK,
I tried to create an excel file, to explain the scenario and the goal.
I have a table with all months (starts on 01/01/2018 this column link to the calendar table), these are the current dates.
I have a column that identifies when I started the sales of products. I can start sales of the product before the start date from the calendar table.
In my example, I have sales the product1 in 2015
I need to calculate the qty month that I have sales of products by
Months.

So on 01/06/2019, I expect to have qty month = 51 to product1. I can't only count of rows, because I don't have rows of years 2015,2016,2017, I need only calculate the qty months starts in Start Sales until the current month.

excel:
https://onedrive.live.com/?authkey=%21AH7UazXoKuMDwkg&id=495BAE4BBBC2D880%214184&cid=495BAE4BBBC2D88...

[dedelman_clng]

Do you need to calculate the number of months for each month, or just a total number of months that the product was for sale?

[twister8889]

I need to calculate the number of the month for each Month column.

For example on 01/04/2019 the Qty Month is 49, 01/05/2019 the Qty Month is 50....