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.
Hi there :),
I am trying to present a measure on a bar chart. The KPI is aggregated in a way that it does a distinctcount over the period of two years to the past and one year to the future. That works fine when presenting it on a card, but when I want to see the above explained aggregated calculation on a time axis, it only seems to reurn the distinctcount for the individual Months.
Here are the formulas of my measures:
==> Number of Brands:
IF( HASONEVALUE( 'Time Dimension'[Date (bins)]); CALCULATE( DISTINCTCOUNT( ´DataTable´[Brand]); DATESBETWEEN( 'Time Dimension'[Date]; [Start-Date(-2to1)]; [End Date(-2to1)]); USERELATIONSHIP(DataTable[Date]; 'Time Dimension'[Date]); DataTable[Brand] <> BLANK()); "Select a Month")
==> Start-Date (-2to1)
Start-Date(-2to1) = DATEADD(VALUES('Time Dimension'[Date (bins)]); -2;YEAR)
==>End-Date(-2to1)
End Date(-2to1) = IF(ISFILTERED('Time Dimension'[Date]) && HASONEVALUE('Time Dimension'[Date]); DATEADD(VALUES('Time Dimension'[Date]); 11; MONTH); TODAY())
Eventhough Start and End Date look different they work just the same and provide a single date value.
Any ideas of how to solve this? A christmas miracle would be nice! 😉
Best regards,
chris
Solved! Go to Solution.
@v-shex-msft, i now found a way how to solve it. So I created a calculated column, with the Start and End Date for each row and instead of using my date table, i used the date column of my data table for the DATESBETWEEN Filter of the Calculate function.
HI @Anonymous,
Can you please share a pbix file with some sample data and expected result for us to testing coding formula?
Regards,
Xiaoxin Sheng
@v-shex-msft, i now found a way how to solve it. So I created a calculated column, with the Start and End Date for each row and instead of using my date table, i used the date column of my data table for the DATESBETWEEN Filter of the Calculate function.
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |