Forum Discussion
Helper IDax formula
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 TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply from bhanu_gautam.
Hi zameenakarmali ,
Please try the following DAX:
Active & Expired =
VAR SelectedQuarter =
MAX ( 'Calender'[Quarter] )
VAR SelectedQtrNo =
LOOKUPVALUE ( 'Calender'[Qtr No], 'Calender'[Quarter], SelectedQuarter )
VAR EndQtrNo =
LOOKUPVALUE (
'Calender'[Qtr No],
'Calender'[Quarter], 'Subscriptions Register'[End Quarter Value]
)
RETURN
IF ( EndQtrNo > SelectedQtrNo, "Active", "Expired" )
If the problem persists, can you provide a .pbix file with sensitive data removed? This will help locate your problem faster.
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.
Helper IThanks, can you let me know how to upload a power bi file?
Hi zameenakarmali ,
you need to upload your file to a cloud storage (like OneDrive, Dropbox, Google Drive or Wetransfer for example) and paste the link to that storage location in the post. A friendly reminder to remember to protect your data privacy.Best Regards,
ZhuHi Zhu,
Please find a link to the file
https://drive.google.com/file/d/17N0nK-tIUTEKVss5hRIMk8JYO5NniFKQ/view?usp=drive_link
Thanks,
Hi powerbiuser1111 ,
You may need to reset it again, I don't have permission to open your file. Thank you in advance for your efforts.
Best Regards,
Zhu
