Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
I have data about opportunities. Important columns for my question are OpportunityId, Date, Stage (in what stage opp is - Contacted, Verbal yes...) and Amount. Whenever something about an opportunity changes, its in new row (unchanged values remain the same).
My assignment is that I have to make a chart (Stacked Column Chart in Power BI Desktop) which shows months on rows, values on columns and stage as legend. Also I have a filter that filteres just rows, that shows last stage of every opportunity in each month (if stage changes in one month, I want to see the last one a the last value). Chart should look like this:
On the first look it seems fine. But when I filter data just for one opportunity, I want to see last stage in every month, not just in months where some change happened:
In the picture above there is no data for May. But the opportunity didn't change since April and in May there should be the same value and stage (as in April).
What should I do?
Thank you very much for your help!
Tereza
Please paste your model layout in here. I want to see how your tables are connected and if you have a proper Date table.
Best
Darek
Hi Darek,
here is my diagram:
Maybe I didn't explain it correctly. Now the model works fine, there is no mistake, I just want a different result. In my fact table there is no data change for the opportunity (selected in the second picture) in May, but I want to see in every month the last stage and amount (even if in that month there is no new data for that opportunity). I filtered some opportunity just for you to see my problem.
Tereza
[Total Amount] = SUM ( FactPipes[Amount] ) -- first basic measure [Last Value in Period] = -- your final measure var __lastVisibleDate = MAX ( DimDate[Date] ) -- DimDate[Date] should be related to FactPipes[Date] var __datesWithValueByOpportunity = GENERATE( VALUES ( DimOpportunity[DimOpportunityId] ), var __lastDateWithValue = CALCULATE ( MAX( FactPipes[Date] ), FactPipes[Date] <= __lastVisibleDate, ALL( DimDate ) ) RETURN { __lastDateWithValue } ) var __lastValueInPeriod = CALCULATE( [Total Amount], TREATAS( __datesWithValueByOpportunity, DimOpportunity[DimOpportunityId], DimDate[Date] ) ) RETURN __lastValueInPeriod
Try this one out.
Best
Darek
I tried it and it killed my tabular instance (repeatedly).
[Total Amount] = SUM ( FactPipes[Amount] ) [Last Value in Period] = var __lastVisibleDate = MAX ( DimDate[Date] ) var __visibleOpportunitiesWithLastDates = FILTER( ADDCOLUMNS( SUMMARIZE( FactPipes, DimOpportunity[DimOpportunityId] ), "LastDate", CALCULATE ( LASTNONBLANK ( DimDate[Date], [Total Amount] ), DimDate[Date] <= __lastVisibleDate ) ), NOT ISBLANK( [LastDate] ) ) var __lastValueInPeriod = CALCULATE( [Total Amount], TREATAS( __visibleOpportunitiesWithLastDates, DimOpportunity[DimOpportunityId], DimDate[Date] ) ) RETURN __lastValueInPeriod
Try the above... How many rows do you have in your fact table?
Best
Darek
My fact table has 59 rows, it is really small and just for testing purposes. The whole model is very small. And my computer is a work computer and I work on it with much bigger databases. Maybe the calculation is too complicated, but I am not an expert in DAX at all, so I don't know how to make it better.
Thank you very much for your help :). If you manage to simplify the formula, it would be awesome.
Best wishes
Tereza
Here's another measure that returns exactly the same results as the previous one (I've created a model and checked it):
Last Value in Period = var __lastVisibleDate = MAX ( DimDate[Date] ) var __visibleOpportunitiesWithLastDates = FILTER( ADDCOLUMNS( VALUES( DimOpportunity[OpportunityId] ), "LastDate", CALCULATE ( LASTNONBLANK( DimDate[Date], [Total Amount] ), DimDate[Date] <= __lastVisibleDate ) ), NOT ISBLANK( [LastDate] ) ) var __lastValueInPeriod = CALCULATE( [Total Amount], TREATAS ( __visibleOpportunitiesWithLastDates, DimOpportunity[OpportunityId], DimDate[Date] ) ) RETURN __lastValueInPeriod
There are no crashes for any of the two measures. They work like magic returning the same thing.
Best
Darek
Hi Darek,
yes, this also works, but it does YTD and I need actual stage of an opportunity:
In this picture you can see that the stages are YTD. But an opportunity has only one stage at a time. I need to see only the max stage for every month. Is that possible?
Thank you 🙂
Tereza
Hi Darek,
I am sorry for the late reply but I was out of office. When I filter just one opportunity (number 1), I see a chart that I send you earlier (tha last picture). And there is for every month every stage that the opportunity have had. I need just the last stage (with the biggest ID). Is it possible?
Thank you,
Tereza