Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have a table with EBITDA values assigned to different dates, some are monthly, some are annually, depending how old they are (the most recent ones are monthly). This data is for 5 different entities.
I am looking for a solution in which I could create a chart, where my values would be presented only for:
a) first date available in my dates column for each entity, depending which entity is selected on the slicer
b) december values for each year between date a) and date c)
c) date selected on the slicer as the current date i.e. latest date looked at
Does anyone knows how to approach this?
@rrwyszynski_p , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Usually in such cases, I build dates
example for year
date = date([year],1,1)
for month
date = date([year], [month],1)
for mmm- yyyy format
date = "01-" & [month Year] //change data type to date.
join with date table and analyze
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
https://drive.google.com/file/d/1LO_EqEs2UhHhjadCw3uLHy_w8OXTHLLE/view?usp=sharing
here is the data and pivots showing the disred outputs on charts depending on slicers (you need to download for pivots)
Hi @rrwyszynski_p ,
Does your problem have been solved? If it is solved, please mark a reply which is helpful to you.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
Put date column into slicer, then select "Before".
Create measure:
Measure =
var MaxDate = EOMONTH(MAXX(ALLSELECTED('source data'),'source data'[date]),0)
var MinDate = EOMONTH(MINX(ALLSELECTED('source data'),'source data'[date]),0)
var Previous =
CALCULATE(
SUM('source data'[value]),
FILTER(
'source data',
'source data'[Year] < YEAR(MaxDate)
&& 'source data'[Month] = 12
|| 'source data'[date] <= MinDate
)
)
var CurrentValue =
CALCULATE(
SUM('source data'[value]),
GROUPBY(
'source data',
'source data'[Entity ID],
'source data'[type]
),
'source data'[Year_month] = FORMAT(MaxDate,"yyyy_m")
)
var result =
IF(
MAX('source data'[Year_month]) = FORMAT(MaxDate,"yyyy_m"),
CurrentValue,
Previous
)
return
IF(
ISFILTERED('source data'[Year]),
result,
Previous + CurrentValue
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.