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.
Hello Power BI Users,
I hope you could help me with DAX measure for my senorio. I have a fact table and date table both are joined with datekey value(which is not aactual date column in fact table). You can see image below. i have a many to one relation on date key column.
I want to create a measure which consider the lastmonth for each quarter value and if the lastmonth of the quarter doesn't is zero or blank then i would consider previous month of that quarter.
How can i achieve this. Please help me out.
Thanks,
Solved! Go to Solution.
@corpsikumar , Create a month start date or YYYYMM and then try like
New column
Month Year = year([Date])*100 + month([Date])
measure
QTD Sales = CALCULATE(lastnonblankvalues(Date[Month Year]),SUM(Sales[Sales Amount])) ,DATESQTD(('Date'[Date])))
@amitchandak is there a way we can color code the values based on last quarter value.
Hi @corpsikumar ,
Try the following formula to see if it meets your needs:
M_ =
CALCULATE (
MAX ( 'Fact Table'[Value] ),
ALLEXCEPT ( 'Date Table', 'Date Table'[Quarter] )
)
If the problem is still not resolved, please point it out. Looking forward to your feedback.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-henryk-mstf ,
Thank you for the response. What if we have multiple products each month. lets say Product-A will have in each month and i would like to get the max value of Product-A in each quarter and if end of the quarter doesn't have any value should consider previous month like Product-A some values in Jan and Feb and doen't have anything for March we should consider feb value for Q1.
Please suggest
@corpsikumar , Create a month start date or YYYYMM and then try like
New column
Month Year = year([Date])*100 + month([Date])
measure
QTD Sales = CALCULATE(lastnonblankvalues(Date[Month Year]),SUM(Sales[Sales Amount])) ,DATESQTD(('Date'[Date])))