Forum Discussion

Ncsbr's avatar
Ncsbr
Frequent Visitor
3 years ago
Solved

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

 

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

     

    • Ncsbr's avatar
      Ncsbr
      Frequent Visitor
      3 years ago

      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