Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Dynamic If and Else with multiple conditions

Hello,
 
I need help to create a DAX measure in Power BI with the following scenario.
 

I have a YearMonth column as a primary filter and PeriodCategory (Rolling 12, QTD, YTD, MTD) filter as secondary.

 

Example1: In YearMonth, If I select 201803 then I select Rolling 12 in PeriodCategory, then it should display the Rolling 12 months from 201803 (i.e., 201803,201802, 201801, 201712, 201711 .... 201704). Which means It has CY 2018 and CY-1 2017 as the result. I have amounts coming from a different table as Currency. Currency table has many columns as CY values are in one column as CurrentYear and CY-1 values are in another column as CurrentYear-1 and CY-2 values are in a different column as CurrentYear-3 and so on. Now, I need to calculate the total some from that respective columns. In our example, we selected 201803 and Rolling 12 as filters. Then I need to add amounts for 201803, 201802 and 201801 from CurrentYear column in the currency table and 201712, 201711, 201710,...201704 from the CurrentYear-1 column in the currency table and sum both the values and display a single value.

 

My data looks like this:

 Table.JPG

Note: Flag, Year and YearMonth are from Date Table and Rest of the CurrentYear amount columns are from Currency table. For better understanding, I put it like this.

 

As per the above example by selecting 201803 in YearMonth and Rolling 12 in PeroidCategory, then the Answer should be the sum of CurrentYear Amount value in Currency table where YearMonth in (201801, 201802, 201803) + Sum of CurrentYear-1 Amount value in Currency table where YearMonth in (201704, 201705, 201705, 201706, 201707, 201708, 201709, 201710, 201711 and 201712).

 

Example 2: If I select the same 201803 in YearMonth and select QTD in PeriodCategory, then there will be only 2018 records (201801,201802 and 201803) which is the current year. So we have to sum only the 3 records and display the answer

All this should be dynamic based on the YearMonth and PeriodCategory selection. So I need this as a measure only but not a calculated column.

 

Thanks in advance!

 

5 REPLIES 5
Anonymous
Not applicable

Hi,

 

I have a YearMonth column as a primary filter and PeriodCategory (Rolling 12, QTD, YTD, MTD) filter as secondary.

 

Example1:

In YearMonth, If I select 201803 then I select Rolling 12 in PeriodCategory, then it should display the Rolling 12 months from 201803 (i.e., 201803,201802, 201801, 201712, 201711 .... 201704). Which means It has CY 2018 and CY-1 2017 as the result. I have amounts coming from a different table as Currency. Currency table has many columns as CY values are in one column as CurrentYear and CY-1 values are in another column as CurrentYear-1 and CY-2 values are in a different column as CurrentYear-3 and so on. Now, I need to calculate the total some from that respective columns.

In our example, we selected 201803 and Rolling 12 as filters. Then I need to add amounts for 201803, 201802 and 201801 from CurrentYear column in the currency table and 201712, 201711, 201710,...201704 from CurrentYear-1 column in the currency table and sum both the values and display a single value.

 

As per the above example by selecting 201803 in YearMonth and Rolling 12 in PeroidCategory, then the Answer should be the sum of CurrentYear Amount where YearMonth in (201801, 201802 and 201803) + CurrentYear-1 Amount where YearMonth in (201704, 201705, 201706, 201707, 201708, 201709, 201710, 201711 and 201712).

 

Example 2: If I select the same 201803 in YearMonth and select QTD in PeriodCategory, then there will be only 2018 records (201801,201802 and 201803) which is the current year. So we have to sum only the 3 records and display the answer.

 

All this should work dynamically based on the YearMonth and PeriodCategory selection. So I need this as a measure but not a calculated column.

 

Can someone please help me with this?

 

Thanks.

v-frfei-msft
Community Support
Community Support

Hi @Anonymous,

 

Kindly share you sample data or pbix to me.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Hi @v-frfei-msft,

 

Thanks for your help. I got this logic works.

 

 

Hi @Anonymous,

 

That's cool, kindly mark the answer as solution to close the case please. Thanks in advance.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

As per the above-mentioned Example 1, I need to sum the values as below picture (CurrentYear + CurrentYear-1)

 CurrentYearCurrentYearCurrentYear-1CurrentYear-1

The final result should be Sum(CurrentYear+CurrentYear-1) = $271,920,278.35.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.