Forum Discussion

zameenakarmali's avatar
zameenakarmali
Icon for Helper I rankHelper I
1 year ago
Solved

Dax formula

I have the following column I have created in Power BI:  Active & Expired = VAR SelectedQuarter = SELECTEDVALUE('Calender'[Quarter]) VAR SelectedQtrNo = LOOKUPVALUE('Calender'[Qtr No], 'Calender'...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi zameenakarmali ,

     

    Since you only want to present these two columns, but in the previous reply Active/Expired was a measure, this would be difficult to achieve out of context, so I'll provide you with an alternative by recreating an Active/Expired calculated column.


    To do this, I've made some changes to the original data:

    1. Added a Sales column to Table.

    2. A new table named Status has been created:

     

    Then here are the specific steps:

    1. Generate a new table, Table 2, which is the Cartesian product of the tables Table and Quarter.

     

    Table 2 = CROSSJOIN('Table','Quarter')

     

     

    2. Create two new columns for Table 2.

    QuarterNumber = LOOKUPVALUE(Dim_Date[Qtr No],Dim_Date[Quarter],'Table 2'[Quarter])

     

    FinalActive/Expired3 = IF('Table 2'[QuarterNumber]>'Table 2'[End Quarter Number],"Active","Expired")

     

     

    3. Create a new relationship:

    4. Create a measure:

     

    Salesvalue = IF(SUM('Table 2'[Sales])=BLANK(),0,IF(HASONEVALUE('Table 2'[Quarter]),SUM('Table 2'[Sales])))

     

     

    5. Create a slicer using the field Quarter from Table 2 and create a table using the field from Status and Salesvalue.

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi  powerbiuser1111 , 

Please refer to the following steps:

 

1.Create a new table, use it as a slicer field:

 

2.Create the relationships:

 

 

3.Create new measures:

Selected Quarter2 = LOOKUPVALUE('Dim_Date'[Qtr No],'Dim_Date'[Quarter],MAX('Quarter'[Quarter]))

 

Active/Expired2 = IF(ISINSCOPE('Quarter'[Quarter]),IF([Selected Quarter2]<CALCULATE(MAX('Table'[End Quarter Number]),REMOVEFILTERS('Dim_Date')),"Active","Expired"))

 

4.The result is as follows:

 

By the way, If you want to compare Starting Quarter and End Quarter in your table you can create two more calculated columns:

 

Start Quarter Number = LOOKUPVALUE('Dim_Date'[Qtr No],'Dim_Date'[Quarter],'Table'[Starting Quarter])

 

Active/Expired3 = IF('Table'[Start Quarter Number]<'Table'[End Quarter Number],"Active","Expired")

 

Result:

Best Regards,
Zhu
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

zameenakarmali's avatar
zameenakarmali
Icon for Helper I rankHelper I
1 year ago

Thanks for that, 

 

If I also have a Sales amount for each contract, and I would like to show just the Sum of Sales for Active and Expired Contracts, how would I do that?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi  zameenakarmali , 

     

    As shown in the image, I added two columns of Contract ID as well as Sales to the original Table:

     

    Then create a measure, use the ALL function to remove all filters to get all active and expired contract sales:

     

    Salesamount = CALCULATE(SUM('Table'[Sales]),ALL('Table'))
     
    The result is as follows:

    Best Regards,
    Zhu

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

     

  • zameenakarmali's avatar
    zameenakarmali
    Icon for Helper I rankHelper I
    1 year ago

    If I was only to show the Sales Amount and the Active/Expired column would that work?

  • zameenakarmali's avatar
    zameenakarmali
    Icon for Helper I rankHelper I
    1 year ago

    I would like to show a table like below: 

     

    The sales amount would change depending on the Selected value of the Quarter.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi zameenakarmali ,

     

    Since you only want to present these two columns, but in the previous reply Active/Expired was a measure, this would be difficult to achieve out of context, so I'll provide you with an alternative by recreating an Active/Expired calculated column.


    To do this, I've made some changes to the original data:

    1. Added a Sales column to Table.

    2. A new table named Status has been created:

     

    Then here are the specific steps:

    1. Generate a new table, Table 2, which is the Cartesian product of the tables Table and Quarter.

     

    Table 2 = CROSSJOIN('Table','Quarter')

     

     

    2. Create two new columns for Table 2.

    QuarterNumber = LOOKUPVALUE(Dim_Date[Qtr No],Dim_Date[Quarter],'Table 2'[Quarter])

     

    FinalActive/Expired3 = IF('Table 2'[QuarterNumber]>'Table 2'[End Quarter Number],"Active","Expired")

     

     

    3. Create a new relationship:

    4. Create a measure:

     

    Salesvalue = IF(SUM('Table 2'[Sales])=BLANK(),0,IF(HASONEVALUE('Table 2'[Quarter]),SUM('Table 2'[Sales])))

     

     

    5. Create a slicer using the field Quarter from Table 2 and create a table using the field from Status and Salesvalue.

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.