Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello good morning.
I have a drawback, and that is that in the analyzes I perform I have 4 mobile periods (MAT or TAM). For example, right now I have the MAT to November 21, to November 20, to November 19 and to November 18. I would like to have a table or measure that calculates the growths period by period. In the posts I have seen, a measure is created that only calculates the variation vs the first period, but in my case I require all the variations (that is, 3 variations).
Thank you very much for your help!!
Solved! Go to Solution.
@Syndicate_Admin , based on what I got. You should create a year column based on these
new column in date table
FY = if(Month([Date])>=11, year([Date]) +1, year([Date]))
Start of year = date([FY]-1, 11,1)
End of year = Date([FY],10, 31)
You can try measures like
Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)
This Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),h'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))
Day of Year =datediff([Year Start date] , [Date],Day) +1
YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))
YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))
Hi, @Syndicate_Admin
May I ask if your problem has been solved? Is the above post helpful to you?
If it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.
If it doesn't, could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented? And It would be great if there is a sample file without any sesentive information here.
It makes it easier to give you a solution.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Syndicate_Admin , based on what I got. You should create a year column based on these
new column in date table
FY = if(Month([Date])>=11, year([Date]) +1, year([Date]))
Start of year = date([FY]-1, 11,1)
End of year = Date([FY],10, 31)
You can try measures like
Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)
This Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),h'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))
Day of Year =datediff([Year Start date] , [Date],Day) +1
YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))
YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |