Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am working on the latest Power BI Desktop (Oct 2021 update) and trying to create a `Clustered Column Chart` to display 2 measures viz., one for SUM(USD_Amount__c), other for COUNT(USD_Amount__c).
I have a Date table named `dimDate` which has a Year offset column named `rYear`. There is a FACT table which contains 2 Date columns viz., `CreatedDate` & `ClosedDate`. The FACT table also contains the `USD_Amount__c` column.
The dimDate table is joined by a relationship to both the CreatedDate & CloseDate columns of the FACT Table like this:
dimDate[Date] -->FACT[CreatedDate]
dimDate[Date]-->FACT[CloseDate]
I want to do something like this to get the 2 measures:
Amt($) = SUM the USD_Amount__c in the FACT table by filtering it on the CreatedDate & CloseDate such that, the CreateDate OR CloseDate are >=2019/01/01 i.e. starting 2 years ago. I dont want to hardcode the date 2019-01-01.
Amt(#) = COUNT the USD_Amount__c in the FACT table by filtering it on the CreatedDate & CloseDate such that, the CreateDate OR CloseDate are >=2019/01/01 i.e. starting 2 years ago. I dont want to hardcode the date 2019-01-01.
Right now, i am trying to use the rYear column in dimDate table to filter the FACT table like this:
I'd start with something like this and adjust as needed:
Opps(#) IT =
VAR StartDate = DATE ( MAX ( dimDate[rYear] ) - 2, 1, 1 )
RETURN
CALCULATE (
COUNT ( 'FACT'[USD_Amount__c] ),
FILTER ( 'FACT', 'FACT'[CloseDate] >= StartDate || 'FACT'[CreatedDate] >= StartDate )
)
Hi @AlexisOlson ,
Thank you for your quick response.
The dimDate[rYear] is a Year offset viz., 1 for next year, 0 for current year, -1 for last year, -2 for prior to last year etc.
Any idea how that can be incorporated in your above formula?
How about YEAR ( MAX ( dimDate[Date] ) ) or YEAR ( TODAY () ) instead of MAX ( dimDate[rYear] )?
If you have an actual year column instead of an offset column, you could use that.
Yes, i have an actual year column.
BTW, YEAR( MAX( dimDate[Date] )) - 2 gives me 2021 as i think the Date table extends to 2023 and so do the CloseDate or CreatedDate columns in FACT table extend to greater than current year. I think what i need is Current Year - 2, isn't it so?
But i do want to make use of the relationship between dimDate and FACT table with Date Slicers. Therefore i need the dimDate table instead of YEAR(TODAY()). How do i do it in your formula?
DimDate does have YEAR column.
Try what I initially suggested with dimDate[Year] (or whatever your actual year column is called) instead of dimDate[rYear].
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
97 | |
79 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |