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, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Base Date(a calculated table):
Base Date = DISTINCT('Table'[Date])
Compare Date(a calculated table):
Compare Date = DISTINCT('Table'[Date])
You may create three measures as below.
Count of Dimensions = COUNTROWS('Table')
Net variance =
IF(
HASONEVALUE('Base Date'[Date])&&HASONEVALUE('Compare Date'[Date]),
COUNTROWS(
FILTER(
'Table',
[Date]=SELECTEDVALUE('Compare Date'[Date])
)
)-
COUNTROWS(
FILTER(
'Table',
[Date]=SELECTEDVALUE('Base Date'[Date])
)
)
)
Actual varaince =
var _comparetab =
CALCULATETABLE(
DISTINCT('Table'[Dimension]),
FILTER(
ALL('Table'),
'Table'[Date]=SELECTEDVALUE('Compare Date'[Date])
)
)
var _basetab =
CALCULATETABLE(
DISTINCT('Table'[Dimension]),
FILTER(
ALL('Table'),
'Table'[Date]=SELECTEDVALUE('Base Date'[Date])
)
)
var _result =
COUNTROWS(
FILTER(
_comparetab,
NOT([Dimension] in _basetab)
)
)
return
IF(
HASONEVALUE('Base Date'[Date])&&HASONEVALUE('Compare Date'[Date]),
IF(
ISBLANK(_result),
0,
_result
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Based on your requirement, I have modified the data as below. The pbix file is attached in the end.
Base Date(a calculated table):
Base Date = DISTINCT('Table'[Date])
Compare Date(a calculated table):
Compare Date = DISTINCT('Table'[Date])
You may modify the measure as below.
Actual varaince =
var _comparetab =
CALCULATETABLE(
DISTINCT('Table'[Dimension]),
FILTER(
ALL('Table'),
'Table'[Date]=SELECTEDVALUE('Compare Date'[Date])
)
)
var _basetab =
CALCULATETABLE(
DISTINCT('Table'[Dimension]),
FILTER(
ALL('Table'),
'Table'[Date]=SELECTEDVALUE('Base Date'[Date])
)
)
var _result =
COUNTROWS(
FILTER(
_basetab,
NOT([Dimension] in _comparetab)
)
)
return
IF(
HASONEVALUE('Base Date'[Date])&&HASONEVALUE('Compare Date'[Date]),
IF(
ISBLANK(_result),
0,
_result
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, count is coming right but I need to variance per employee but data is poping for every employee .
I have also replaced the country of A(india ) to A(USA) on 06/01/19
Net variance is coming wrong by selecting groups in slicer
Hi, @Anonymous
You may try the following measure to see if it works.
Actual varaince =
var _comparetab =
CALCULATETABLE(
DISTINCT('Table'[Dimension]),
FILTER(
ALLSELECTED('Table'),
'Table'[Date]=SELECTEDVALUE('Compare Date'[Date])
)
)
var _basetab =
CALCULATETABLE(
DISTINCT('Table'[Dimension]),
FILTER(
ALLSELECTED('Table'),
'Table'[Date]=SELECTEDVALUE('Base Date'[Date])
)
)
var _result =
COUNTROWS(
FILTER(
_basetab,
NOT([Dimension] in _comparetab)
)
)
return
IF(
HASONEVALUE('Base Date'[Date])&&HASONEVALUE('Compare Date'[Date]),
IF(
ISBLANK(_result),
0,
_result
)
)
Best Regards
Allan
Hi,
Thanks for your help but its not working while comparing 1/1/19 and 6/1/19 its giving different value when selecting india and usa as countries
@Anonymous , refer if this blog can help
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
You can use single date if needed
This completely different for what I am looking for I need to calculate variance and i have many dimesnsion for different dates
@Anonymous Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
for base date =1/1/2019(A,B) and compare date= 6/1/2019 (A,B,B,F) ACTUAL VARINANCE =1 as F is not present in 1/1/2019 but actual varaince for base date = 6/1/2019 (A,B,B,F) and compair date =1/12019(A,B,) is 0 as both A,B is present in base date
I need to calculate actaul variance and net variance by slected 2 dates from slicer for eg(base date = 1/1/2019 and coampare date = 6/1/2019 actual variance =1 and net =2 but for base date = 6/1/2019 and copare date 1/1/2019 actaul variance =0 and net variance =2)
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |