cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Difference Between First Month and Current Month in a YTD Id Count

Hi,

I'm trying to calculate the difference between the count of Id's of the first month of the year and the current month count, but I'm having some issues to get it.

As the example below, I'm trying to calculate the difference between 2022/Jan and 2022/Nov that should be (14644-13471) = 903

I tryed to create measures with MIN and MAX function together with Count measure but it's cant be done because of min and max most work on Values from Columns.

Can someone help me with that?

In the end I just want to show the result from the years, in 2022, following the example it's (14644-13471) = 903

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Ncsbr ,

Please refer to it to see if it helps you.

Create a column.

``month = MONTH('Table'[date])``

Then create a measure.

``````Measure = var _year=YEAR(TODAY())
var _yeardate=YEAR(MAX('Table'[date]))
var _1=MAXX(FILTER(ALL('Table'),_yeardate=_year),'Table'[month])
var _2=MINX(FILTER(ALL('Table'),_yeardate=_year),'Table'[month])
var _maxdate=CALCULATE(MAX('Table'[count]),FILTER(ALL('Table'),'Table'[month]=_1))
var _mindate=CALCULATE(MAX('Table'[count]),FILTER(ALL('Table'),'Table'[month]=_2))
return
_maxdate-_mindate``````

The result is 14644-13741=903.

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

3 REPLIES 3
Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

Hi @Ncsbr ,

Please refer to it to see if it helps you.

Create a column.

``month = MONTH('Table'[date])``

Then create a measure.

``````Measure = var _year=YEAR(TODAY())
var _yeardate=YEAR(MAX('Table'[date]))
var _1=MAXX(FILTER(ALL('Table'),_yeardate=_year),'Table'[month])
var _2=MINX(FILTER(ALL('Table'),_yeardate=_year),'Table'[month])
var _maxdate=CALCULATE(MAX('Table'[count]),FILTER(ALL('Table'),'Table'[month]=_1))
var _mindate=CALCULATE(MAX('Table'[count]),FILTER(ALL('Table'),'Table'[month]=_2))
return
_maxdate-_mindate``````

The result is 14644-13741=903.

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

Frequent Visitor

Hello @Anonymous and @Ashish_Mathur,

Thanks for the answers, the @Anonymous was the first step for the answers that I needed, but at the end I went to another way to solve my YTD issue.

The job was to make a YTD view that compare the Ids counts of which year to the last month of the last year. So I had to compare the count of dez/2022 for which month of this years so.

The way that I used was:
1) Create a summary table thats shows the information of dezember count of all years

for that, the code came with CALCULATETABLE and SUMMIRIZE, to use filters to get only the dezember counts.

Ref YTD Dezembro Geral = CALCULATETABLE(SUMMARIZE(V2_Total_Contas_Ativas,V2_Total_Contas_Ativas[Mes].[Date],"N Contas",DISTINCTCOUNT(V2_Total_Contas_Ativas[Id Conta]),"Mês",MONTH(V2_Total_Contas_Ativas[Mes].[Date])),MONTH(V2_Total_Contas_Ativas[Mes]) = 12)

2) From the Ref YTD, I used LOOKUPFOR in another table to get beck the YTD dezember, then I could divide which month count to dez ref count of the last year to get the YTD the was they asked me.

the codes were:

Dez de Referencia = LOOKUPVALUE('Ref YTD Dezembro Geral'[N Contas],'Ref YTD Dezembro Geral'[Ano referencia YTD],'Estudo YTD Geral'[Ano])

Ano Ref = FORMAT('Estudo YTD Geral'[Date],"YY")-1 << That brings the count of dez of last year on "Dez de Referencia" column,

Once again, thanks for all ;)!
The problem was solved

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.