last month for each quarter value DAX

[corpsikumar]

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,

[corpsikumar]

@amitchandak is there a way we can color code the values based on last quarter value. 

[v-henryk-mstf]

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.

[corpsikumar]

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

[amitchandak · Accepted 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])))