Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
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.
Hi,
Share the download link of your PBI file.
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.
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.
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:
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |