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 ,
I have months under slicer and i want to arrive difference between two selected period based on the selection.
(Based on the latest month selcted and selected previous period)
Arriving perivious period value i am having zero with below dax calculatrion. can anyone help me out.
Previous period calculation i am not getting right values.
Thanks,
Mani
Solved! Go to Solution.
Hi @Mani_ms ,
I found that you filtered the group = BS in the matrix so I created a slicer for the group field and the following measure if I got it correctly:
Measure =
VAR _group =
SELECTEDVALUE ( 'FMA 011 CY'[Group] )
VAR _maxfis =
CALCULATE (
MAX ( 'FMA 011 CY'[Fiscal Year/Period] ),
ALLSELECTED ( 'FMA 011 CY'[Fiscal Year/Period] )
)
VAR _minfis =
CALCULATE (
MIN ( 'FMA 011 CY'[Fiscal Year/Period] ),
ALLSELECTED ( 'FMA 011 CY'[Fiscal Year/Period] )
)
VAR _max =
CALCULATE (
SUM ( 'FMA 011 CY'[Balance - Cumulative] ),
'FMA 011 CY'[Group] = _group,
'FMA 011 CY'[Fiscal Year/Period] = _maxfis
)
VAR _min =
CALCULATE (
SUM ( 'FMA 011 CY'[Balance - Cumulative] ),
'FMA 011 CY'[Group] = _group,
'FMA 011 CY'[Fiscal Year/Period] = _minfis
)
RETURN
_max - _min
Attached sample file: fille.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Mani_ms ,
Try like this measure:
Diff =
VAR _max =
CALCULATE ( MAX ( 'Table'[Sales] ), ALLSELECTED ( 'Table'[Month] ) )
VAR _min =
CALCULATE ( MIN ( 'Table'[Sales] ), ALLSELECTED ( 'Table'[Month] ) )
RETURN
_max - _min
Attached a sample file that hopes to help you: Comparing Two period slicer selection.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thanks for your inputs, seems somehow i am missing the expected result. attached PBI file
Expected result
would be great if you could help on this?
Thanks,
Mani
Hi @Mani_ms ,
Not certain how did your expected output calculate, for each group and slected month?
Could you please sharing a dummy .pbix file for further discussion? Remeber to replace the sensitive information in the file.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@v-yingjl Thanks for your input.
I am new to community , How to upload files in the community portal. i have business one drive there i can't share the information outside. could you pls suggest alernate way.
Hi @Mani_ms ,
You can upload the dummy file to OneDrive for Business etc which can store files and just share this file link in the community.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Please find the below dummy file for your reference. would be great if you could assist on this.
Thanks
Mani
Hi @Mani_ms ,
I found that you filtered the group = BS in the matrix so I created a slicer for the group field and the following measure if I got it correctly:
Measure =
VAR _group =
SELECTEDVALUE ( 'FMA 011 CY'[Group] )
VAR _maxfis =
CALCULATE (
MAX ( 'FMA 011 CY'[Fiscal Year/Period] ),
ALLSELECTED ( 'FMA 011 CY'[Fiscal Year/Period] )
)
VAR _minfis =
CALCULATE (
MIN ( 'FMA 011 CY'[Fiscal Year/Period] ),
ALLSELECTED ( 'FMA 011 CY'[Fiscal Year/Period] )
)
VAR _max =
CALCULATE (
SUM ( 'FMA 011 CY'[Balance - Cumulative] ),
'FMA 011 CY'[Group] = _group,
'FMA 011 CY'[Fiscal Year/Period] = _maxfis
)
VAR _min =
CALCULATE (
SUM ( 'FMA 011 CY'[Balance - Cumulative] ),
'FMA 011 CY'[Group] = _group,
'FMA 011 CY'[Fiscal Year/Period] = _minfis
)
RETURN
_max - _min
Attached sample file: fille.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi Yingjie Li,
Thanks for your answer, seems to have worked for others!
I am trying to do the same thing, but have limited experience in writing DAX, can you share your pbix file again?
Thanks a lot!
@v-yingjl thank you very much. Its perfectly working as per my expectation..Thank you once again
Hi,
Thanks for your input.
Its working for the Max and Min values and difference is am getting wrong number.
@Mani_ms You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
But, 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.
Hi,
Material quite useful. Ultimately what i am trying to get output is based on the selected months want to identify the difference between the selected value .
would be great if you could help on that.
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |