Forum Discussion
If command
Hello,
I want to define no. of weeks for two cases in the table 'Geschäftskunden', but I get error:
Anzahl Woche =
VAR _StartDate = SELECTEDVALUE('Geschäftskunden'[Startdatum])
VAR _EndDate = SELECTEDVALUE('Geschäftskunden'[Enddatum])
RETURN
IF (_EndDate = ISBLANK,
DATEDIFF(_StartDate, Today(), WEEK)
)
,
IF (_EndDate = NOT ISBLANK,
DATEDIFF(_StartDate, _EndDate, WEEK)
)
Anonymous , Try using
DAX
Anzahl Woche =
VAR _StartDate = SELECTEDVALUE('Geschäftskunden'[Startdatum])
VAR _EndDate = SELECTEDVALUE('Geschäftskunden'[Enddatum])
RETURN
IF (
ISBLANK(_EndDate),
DATEDIFF(_StartDate, TODAY(), WEEK),
DATEDIFF(_StartDate, _EndDate, WEEK)
)Hello Anonymous,
Thank you for reaching out to the Microsoft Fabric Forum Community.I have reproduced your scenario in Power BI Desktop using sample data, and I can confirm that the calculation is working as expected.
Summary of what I did:
- Created a sample table Geschäftskunden with both populated and blank Enddatum values.
- Used the following DAX formula in a calculated column to handle both cases correctly:
Anzahl Woche = VAR _StartDate = 'Geschäftskunden'[Startdatum] VAR _EndDate = 'Geschäftskunden'[Enddatum] RETURN IF( ISBLANK(_EndDate), DATEDIFF(_StartDate, TODAY(), WEEK), DATEDIFF(_StartDate, _EndDate, WEEK) )Result:
The column Anzahl Woche now shows the expected number of weeks:- When Enddatum is blank, it uses Today().
- When Enddatum is present, it uses Enddatum.
I am attaching the sample .pbix file here for your reference so you can review and adapt it to your own dataset.
Thank you, bhanu_gautam & Ritaf1983 for sharing valuable insights.If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
7 Replies
Hi Anonymous
The error comes from incorrect ISBLANK syntax and an extra IF. In DAX you must call ISBLANK() as a function, and IF takes three arguments (condition, value-if-TRUE, value-if-FALSE). A single expression does the job:
Anzahl Woche =
VAR _StartDate = SELECTEDVALUE ( 'Geschäftskunden'[Startdatum] )
VAR _EndDate = SELECTEDVALUE ( 'Geschäftskunden'[Enddatum] )
RETURN
IF (
ISBLANK ( _EndDate ),
DATEDIFF ( _StartDate, TODAY(), WEEK ),
DATEDIFF ( _StartDate, _EndDate, WEEK )
)Things to double-check
Both columns are of Date data type.
If _StartDate might also be blank, wrap it in ISBLANK or use COALESCE.
For deeper troubleshooting, it’s best to share a sample PBIX or at least some mock data—upload to a public drive (OneDrive, Google Drive, etc.) and post the link.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Anonymous , Try using
DAX
Anzahl Woche =
VAR _StartDate = SELECTEDVALUE('Geschäftskunden'[Startdatum])
VAR _EndDate = SELECTEDVALUE('Geschäftskunden'[Enddatum])
RETURN
IF (
ISBLANK(_EndDate),
DATEDIFF(_StartDate, TODAY(), WEEK),
DATEDIFF(_StartDate, _EndDate, WEEK)
)Hello Anonymous,
Thank you for reaching out to the Microsoft Fabric Forum Community.I have reproduced your scenario in Power BI Desktop using sample data, and I can confirm that the calculation is working as expected.
Summary of what I did:
- Created a sample table Geschäftskunden with both populated and blank Enddatum values.
- Used the following DAX formula in a calculated column to handle both cases correctly:
Anzahl Woche = VAR _StartDate = 'Geschäftskunden'[Startdatum] VAR _EndDate = 'Geschäftskunden'[Enddatum] RETURN IF( ISBLANK(_EndDate), DATEDIFF(_StartDate, TODAY(), WEEK), DATEDIFF(_StartDate, _EndDate, WEEK) )Result:
The column Anzahl Woche now shows the expected number of weeks:- When Enddatum is blank, it uses Today().
- When Enddatum is present, it uses Enddatum.
I am attaching the sample .pbix file here for your reference so you can review and adapt it to your own dataset.
Thank you, bhanu_gautam & Ritaf1983 for sharing valuable insights.If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
