Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I’m facing a scenario where I’m not finding a possible solution. My data is in the following manner:
Jun-23 | A1 | 08-06-2023 | 200 |
Jun-23 | A2 | 10-06-2023 | 300 |
Jun-23 | A3 | 10-05-2023 | 400 |
Jul-23 | A1 | 08-06-2023 | 200 |
Jul-23 | A2 | 10-06-2023 | 300 |
Jul-23 | A4 | 14-07-2023 | 500 |
Jul-23 | A5 | 22-07-2023 | 600 |
Aug-23 | A2 | 10-06-2023 | 300 |
Aug-23 | A4 | 14-07-2023 | 500 |
Aug-23 | A6 | 02-08-2023 | 900 |
Aug-23 | A7 | 17-08-2023 | 1000 |
Now the task to be performed is to compare the data of any selected month to its previous month and bring out the transactions which are cleared or not using Tran ID value.
The canvas should have a Source Data Period Slicer (through which the end user can select any period), Card Visual showing No of Cleared items (Transactions available in previous month but not available in current month), Card Visual showing No of Open items (Transactions available in previous month and current month), and Card Visual showing No of Newly Added items (Transactions not available in previous month but are available in current month)
Suppose if the user selects the month of Aug-23, then the result should show that there are 2 transactions which are cleared in the month of July (A1 and A5), 2 transactions are pending since July through Aug (A2 and A4), and 2 transactions are newly added in the month of Aug (A6 and A7)
Another Example: if the user selects the month of July, then the result should show that there is 1 transaction cleared in the month of June (A3), 2 transactions are pending since June through July (A1 and A2), and 2 transactions are newly added in the month of July (A4 and A5)
I’ve tried creating calculated tables, different types of measures but none of the approaches are working and I’m not able to achieve the desired output. Anyone please help.
Solved! Go to Solution.
Hi @HareshChalla ,
I solved this by creating a separate Data Period table as a slicer.
As you can see, it's Date type.
The Period column in the main table is also a date type. If your column is of type text, set it to a date type, or try replacing it with a date column in the formulas below.
No relationship:
I created the following measures.
Cleared items =
VAR _SEL=SELECTEDVALUE('Table 2'[Data Period])
VAR _PRE=EOMONTH(_SEL,-1)
VAR _PREVIOUSYEAR=YEAR(_PRE)
VAR _PREVIOUSMONTH=MONTH(_PRE)
VAR _CURRENT=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&[Data Period]=_SEL))
VAR _PREVIOUS=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&YEAR([Data Period])=_PREVIOUSYEAR&&MONTH([Data Period])=_PREVIOUSMONTH))
VAR _IF=IF(_PREVIOUS+_CURRENT=_PREVIOUS,_PREVIOUS+_CURRENT)
RETURN _IF
No of Cleared items = CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER('Table',[Cleared items]>0))
Open items =
VAR _SEL=SELECTEDVALUE('Table 2'[Data Period])
VAR _PRE=EOMONTH(_SEL,-1)
VAR _PREVIOUSYEAR=YEAR(_PRE)
VAR _PREVIOUSMONTH=MONTH(_PRE)
VAR _CURRENT=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&[Data Period]=_SEL))
VAR _PREVIOUS=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&YEAR([Data Period])=_PREVIOUSYEAR&&MONTH([Data Period])=_PREVIOUSMONTH))
VAR _IF=IF(_PREVIOUS=_CURRENT,_CURRENT)
RETURN _IF
No of Open items = CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER('Table',[Open items]>0))
Newly Added items =
VAR _SEL=SELECTEDVALUE('Table 2'[Data Period])
VAR _PRE=EOMONTH(_SEL,-1)
VAR _PREVIOUSYEAR=YEAR(_PRE)
VAR _PREVIOUSMONTH=MONTH(_PRE)
VAR _CURRENT=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&[Data Period]=_SEL))
VAR _PREVIOUS=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&YEAR([Data Period])=_PREVIOUSYEAR&&MONTH([Data Period])=_PREVIOUSMONTH))
VAR _IF=IF(_PREVIOUS+_CURRENT=_CURRENT,_CURRENT)
RETURN _IF
No of Newly Added items = CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER('Table',[Newly Added items]>0))
The result is as follows.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @HareshChalla ,
I solved this by creating a separate Data Period table as a slicer.
As you can see, it's Date type.
The Period column in the main table is also a date type. If your column is of type text, set it to a date type, or try replacing it with a date column in the formulas below.
No relationship:
I created the following measures.
Cleared items =
VAR _SEL=SELECTEDVALUE('Table 2'[Data Period])
VAR _PRE=EOMONTH(_SEL,-1)
VAR _PREVIOUSYEAR=YEAR(_PRE)
VAR _PREVIOUSMONTH=MONTH(_PRE)
VAR _CURRENT=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&[Data Period]=_SEL))
VAR _PREVIOUS=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&YEAR([Data Period])=_PREVIOUSYEAR&&MONTH([Data Period])=_PREVIOUSMONTH))
VAR _IF=IF(_PREVIOUS+_CURRENT=_PREVIOUS,_PREVIOUS+_CURRENT)
RETURN _IF
No of Cleared items = CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER('Table',[Cleared items]>0))
Open items =
VAR _SEL=SELECTEDVALUE('Table 2'[Data Period])
VAR _PRE=EOMONTH(_SEL,-1)
VAR _PREVIOUSYEAR=YEAR(_PRE)
VAR _PREVIOUSMONTH=MONTH(_PRE)
VAR _CURRENT=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&[Data Period]=_SEL))
VAR _PREVIOUS=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&YEAR([Data Period])=_PREVIOUSYEAR&&MONTH([Data Period])=_PREVIOUSMONTH))
VAR _IF=IF(_PREVIOUS=_CURRENT,_CURRENT)
RETURN _IF
No of Open items = CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER('Table',[Open items]>0))
Newly Added items =
VAR _SEL=SELECTEDVALUE('Table 2'[Data Period])
VAR _PRE=EOMONTH(_SEL,-1)
VAR _PREVIOUSYEAR=YEAR(_PRE)
VAR _PREVIOUSMONTH=MONTH(_PRE)
VAR _CURRENT=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&[Data Period]=_SEL))
VAR _PREVIOUS=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&YEAR([Data Period])=_PREVIOUSYEAR&&MONTH([Data Period])=_PREVIOUSMONTH))
VAR _IF=IF(_PREVIOUS+_CURRENT=_CURRENT,_CURRENT)
RETURN _IF
No of Newly Added items = CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER('Table',[Newly Added items]>0))
The result is as follows.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |