Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a date slicer to control and update all the KPI ratios in my visual. When I choose a certain date range, one of my KPI ratios should show the total count within my selected date range (e.g Start: 2020-07-04, End: 2020-07-31). Another ratio should be showing the same period before (e.g Start: 2020-06-07, End: 2020-07-04). But in my current situation, I see both ratios are the same (current vs previous). Here is my DAX calculation for the previous period ratio
num_num_in_Previous_Period = VAR num_Start_Period = MIN(SN_Incident[sys_created_on]) VAR num_End_Period = MAX(SN_Incident[sys_created_on]) var num_Period_Diff = DATEDIFF( num_Start_Period, num_End_Period, DAY) VAR num_End_of_Previous_Period = num_Start_Period VAR num_Start_of_Previous_Period = num_End_of_Previous_Period - num_Period_Diff RETURN CALCULATE( SUM(SN_Incident[num]), FILTER(SN_Incident, num_Start_of_Previous_Period <= SN_Incident[sys_created_on])) 7
Solved! Go to Solution.
// Such calculations should be performed with
// a Date table in the model. I suspect your
// model consists of one big table, right?
// If this is the case, then please be aware
// that you're not doing it right. To know
// how to correctly model data in Power BI
// please read upon dimensional modeling or
// watch some YT videos where such things
// are discussed at length. If you don't follow
// Best Practices, be prepared for some nasty
// bugs that you'll not even be able to
// diagnose. You'll not even be aware that
// they exist.
// But here's the measure you're asking for
// in this "dirty" model:
[Your Measure] =
VAR __start = MIN( SN_Incident[sys_created_on] )
VAR __end = MAX( SN_Incident[sys_created_on] )
VAR __dayDiff = DATEDIFF( __start, __end, DAY )
VAR __endPrev = __start
VAR __startPrev = __endPrev - __dayDiff
VAR __result =
CALCULATE(
SUM( SN_Incident[num] ),
__startPrev <= SN_Incident[sys_created_on],
SN_Incident[sys_created_on] <= __endPrev
)
RETURN
__result
// Such calculations should be performed with
// a Date table in the model. I suspect your
// model consists of one big table, right?
// If this is the case, then please be aware
// that you're not doing it right. To know
// how to correctly model data in Power BI
// please read upon dimensional modeling or
// watch some YT videos where such things
// are discussed at length. If you don't follow
// Best Practices, be prepared for some nasty
// bugs that you'll not even be able to
// diagnose. You'll not even be aware that
// they exist.
// But here's the measure you're asking for
// in this "dirty" model:
[Your Measure] =
VAR __start = MIN( SN_Incident[sys_created_on] )
VAR __end = MAX( SN_Incident[sys_created_on] )
VAR __dayDiff = DATEDIFF( __start, __end, DAY )
VAR __endPrev = __start
VAR __startPrev = __endPrev - __dayDiff
VAR __result =
CALCULATE(
SUM( SN_Incident[num] ),
__startPrev <= SN_Incident[sys_created_on],
SN_Incident[sys_created_on] <= __endPrev
)
RETURN
__result
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.
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |